Dropdown not populating SP List field from choice in related SP list

[aaronhowe1]

I realise this question has been asked - but sadly having followed all of the other advice given, I've yet to get a satisfactory result.

I have two SP Lists - Staff Data and CRM Data, and use custom forms in both.  In the CRM Data list I needed to add a button to transfer an entry to a new site manager, and have successfully set up all of the fields except for the Site Manager field.  Site manager is populated from the Staff Data List in order to maintain referential integrity (the data also feeds a BI report).

In the Staff Data list the field is named Staff Name, the field itself is Title (of course).  The equivalent field it feeds (through a Choice column) is named Site Manager and the field is SiteManager.

The Combobox has the following settings:

Data Source - 

Distinct(

             Sort(

                     'Staff Data',

                      'Staff Name',

                      SortOrder.Ascending

                      ),

            Staff Name'

)

DefaultSelectedItems - Parent.Default (I have changed this and Default before but neither worked)

DisplayFields - ["Value"]

Everything displays correctly, it's the commit that is the issue.  Therefore...

In the DataCard:

DataField - "SiteManager"

Default - ThisItem.'Site Manager'

DisplayName - 

DataSourceInfo([@'CRM Data'],DataSourceInfo.DisplayName,"SiteManager")

Update - {Value: DataCardValue7.Selected.Value}

I have deleted the DataCard and re-entered it but without success.  What is peculiar, is that the combobox appears to have a dependency even though I didn't give it one.  The Depends On link is clickable and shows a Parent Control of DataCardValue22 (a completely unrelated item) with a Matching Field of Staff Name/Value.  I can't remove this dependency and wonder if it may be causing the issue - especially as it came back once the control had been deleted and re-entered?

[Amik]

@aaronhowe1 -

As I understand it so far, you have two SharePoint lists: List1 (Staff Data) and List2 (CRM Data).

In List2, you have a LookUp field called "Site Manager". The LookUp source is from List1, the Logical Name of the related field is "Title", and the Display Name is "Staff Name" (not "Staff Data" as I think you have incorrectly stated in your post).

In your Edit Form control, you have added this LookUp column as a DataCard into your Form. For whatever reason which is not clear, you have discarded the default properties of the DataCard and made several modifications.

I suspect the reason you have made these modifications is because the Title field in List1 contains duplicate names.

What you're trying to do:

You want to submit the selected ComboBox value into the Site Manager LookUp field in List2, but you also want to ensure the ComboBox displays distinct LookUp results only.

Is that correct?

[aaronhowe1]

Yes that's correct, I've edited the original post to fix the error.

[aaronhowe1]

I've tried a few troubleshooting methods around the Update field of the DataCard, which definitely seems to be where the issue is - changing the Value type committed, changing it to a fixed value rather than the dropdown value with an item that I know exists in the list already - nothing commits, it just leaves the field blank.

This seems to indicate that a SP List choice field doesn't like being written to this way.  Should I instead be considering a patch?  I'm really reluctant to do it that way if I'm just missing a syntax option.

[Amik]

@aaronhowe1 -

Short answer:

Please remove the DataCard from your EditForm control, then add the DataCard back into your Form and unlock it. This is to eliminate any other modifications you may have made.

Method one:

I would expect the Items property of the ComboBox control to contain:

Sort(
    Distinct(
        'CRM Data',
        'Site Manager'.Value
    ),
    Value,
    SortOrder.Ascending
)

In the Update property of the DataCard, use:

LookUp(
    Choices([@'CRM Data'].'Site Manager'),
    Value = DataCardValue7.Selected.Value
)

Do not make any other modifications to the DataCard.

Method two:

Apply the below to the Items property of the ComboBox control:

DropColumns(
    AddColumns(
        GroupBy(
            Choices([@'CRM Data'].'Site Manager'),
            "Value",
            "GroupedItems"
        ),
        "Id",
        First(GroupedItems).Id
    ),
    "GroupedItems"
)

Do not make any other modifications to the DataCard.

Long answer:

One of the most common causes for problems I see on the forums is not understanding the difference between a record and a value.

A SharePoint LookUp column is a complex field type. A complex field type requires a record, and a record is a single item in a Table.

Different complex field types will require different attributes in the record. For example, a People Picker field is a complex field type, and it requires a record value with the following attributes in the schema:

• Claims
• Department
• DisplayName
• Email
• Job Title
• Picture

The same principle applies to a SharePoint LookUp Column, which requires the following attributes:

• Id
• Value

The Distinct function returns a single column table called "Value". This function strips out all other attributes in the record table, and returns only the distinct items from the column you specify. 

Because the Items property of your Combobox/Dropdown control is no longer returning a record, you will receive an error because the LookUp field is expecting a record.

We accommodate for this by either using method one, which leverages the LookUp function return the correct record. 

Alternatively, we can use "transform" or "shaping" functions like GroupBy, AddColumns, and DropColumns to create a Table which matches the schema/column structure that the field is expecting us to provide. For a SharePoint LookUp column, this would be the "Id" and "Value" column.

Further reading:

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/working-with-tables 

[aaronhowe1]

Thanks, that had some mixed success.  Option 1 did not work, option 2 did but as it only pulls existing data from the list it's already in, it doesn't account for new items added in Staff Data which need to be referenced in CRM data - the form is to accomodate the transfer of records from one person to another at a fixed date and so there will almost always be new names to be added to CRM data.

So whereas I understand the need to extract table data, I had hoped the process of taking the literal value from the combobox would have been more straightforward.  I may just end up using an ugly collect/patch method instead.

[Amik]

@aaronhowe1 - sorry I missed this response.

"Option 1 did not work" is not very descriptive. 

Regarding Option 2, unfortunately I do not understand what you mean.

I suspect there is something you're trying to do with your app which you have not clarified. The solutions posted above will work based on my understanding of what you're trying to do.