Submitting Custom SharePoint Forms (via PowerApp) with Lookup data types

[Kalki]

I've got a fairly simple form for me to fill out on SharePoint.  Four form fields total, two are dependencies.

Name, TIN, Ad No., and My No. 

These four columns are all lookup types.  The first two reference a master list of names (Title column) and TINs (TIN column) on a SharePoint List "Address Log (Map)" - both type Text on source list.  The second two fields also reference a separate SharePoint List "Pursuits Advertisements Log" (Ad No. column and My No. column), also text data types. 

Unfortunately, the lookup fields won't transfer to my SharePoint List when I hit submit on the published form. Therefore, I tried updating the "Update" property for one of the fields as a test. But I can't figure it out. 

Here is how I set up my formulas on PowerApps. They are all combo boxes.

Name (NameLU)_DataCard1

DataCardValue1

No dependencies

• Items = Distinct('Address Log (Map)',Title)
• DisplayFields = [""]

TIN_DataCard1

DataCardValue2

Depends on DataCardValue1

• Items = Filter('Address Log (Map)', Title = DataCardValue1.Selected.Value)
• DisplayFields = ["TIN"]

Ad No._DataCard1

DataCardValue3

No dependencies

• Items Distinct('Pursuits Advertisements Log','Ad No.')
• DisplayFields= ["Value"]

My No.

DataCardValue7

Depends on DataCardValue3

• Items = Filter('Pursuits Advertisements Log', 'Ad No.' = DataCardValue3.Selected.Value)
• DisplayFields = ["My No."]

This all works great within the PowerApp designer interface.  However, like I mentioned, the lookup fields won't transfer to my SharePoint List when I hit submit on the published form.

Here is my "Update" property attempt:

For the Name form field's "Update" property, the formula below normally has "ID" where my first red "Value" is located (below).  But that doesn't populate anything in Intellisense. For the second red "Value" (also below), I tried replacing it with "Title" from my original source data but that didn't do anything either.  Intellisense wanted to put Value for both slots.  After trying multiple different variations, I'm stuck.  I'd be grateful for some help.

Update =

{

    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

    Id: DataCardValue1.Selected.Value,

    Value: DataCardValue1.Selected.Value

}

[WarrenBelz]

Hi @Kalki ,

I will firstly (hopefully) give you the solution

{
   Value: DataCardValue1.Selected.Value,
   Id:
   LookUp(
      'Address Log (Map)',
      Title = DataCardValue1.Selected.Value
   ).ID
}

and then a bit of advice on Lookup columns. They are generally unnecessary with Power Apps and can cause you many future frustrations and restrictions. I do not use them and probably never will.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Kalki]

Hi @WarrenBelz!

Thank you so much for your input. I love the lookup columns for the excel-like data entry also. I was really hoping to have both.
Also, your solution worked for the first of the four "Update = " inputs!

I tried the follow suit for TIN, Ad No., and My No. However, my attempts to translate your solution didn't work. Both TIN and My No. come up with “.Value” errors.  Both are dependent on the field above them. (TIN is dependent on Name and My No. is dependent on Ad No.).

Thoughts on what I’m doing wrong? Here is what I put in:

 TIN:  Update =

{

   Value: DataCardValue2.Selected.Value,

   Id:

   LookUp(

      'Address Log (Map)',

      Title = DataCardValue2.Selected.Value

   ).ID

}

Issue: Red squiggly lines under “.Value” - “Name isn’t valid. ‘Value’ Isn’t recognized”

Ad No. Update =

{

   Value: DataCardValue3.Selected.Value,

   Id:

   LookUp(

      'Pursuits Advertisements Log',

      Title = DataCardValue3.Selected.Value

   ).ID

}

Issue: No errors are found in the calculation but nothing shows after submitting info in the published form. 

My No. Update =

{

   Value: DataCardValue7.Selected.Value,

   Id:

   LookUp(

      'Pursuits Advertisements Log',

      Title = DataCardValue7.Selected.Value

   ).ID

}

Issue: Red squiggly lines under “.Value” - “Name isn’t valid. ‘Value’ Isn’t recognized”

[WarrenBelz]

Hi @Kalki ,

What is the Items of those Combo Boxes and what is the field name displayed in them ?

[Kalki]

@WarrenBelz

I'm a little confused on what you mean by "field name displayed in them", so please let me know if what I provided below isn't what you needed. 

TIN_DataCard1

      DataCardValue2

         Items = Filter('Address Log (Map)', Title = DataCardValue1.Selected.Value)

         DisplayFields = ["TIN"]

Field name on my list: "LookupTIN"

Field name of reference list that the lookup column finds: "TIN"

AD No._DataCard1

   DataCardValue3

        Items = Distinct('Pursuits Advertisements Log','Ad No.')

        DisplayFields = ["Value"]

Field name on my list: "AdNo_x002e__LU"

Field name of reference list that the lookup column finds: "AdNo_x002e_"

My No._DataCard1

     DataCardValue7

         Items = Filter('Pursuits Advertisements Log', 'Ad No.' = DataCardValue3.Selected.Value)

         DisplayFields = ["MyNo_x002e_"]

Field name on my list: "MyNo_x002e__LU"

Field name of reference list that the lookup column finds: "MyNo_x002e_"

[WarrenBelz]

Hi @Kalki ,

You have provided it - (the DisplayFields) - for the TIN Update

{
   Value: DataCardValue2.Selected.TIN,
   Id:
   LookUp(
      'Address Log (Map)',
      Title = DataCardValue2.Selected.TIN
   ).ID
}

'My No.' Update

{
   Value: DataCardValue7.Selected.'My No.',
   Id:
   LookUp(
      'Pursuits Advertisements Log',
      Title = DataCardValue7.Selected.'My No.'
   ).ID
}

I cannot see anything wrong with 'Ad No.' in the structure, however you are referencing the Title column in all three, so I am assuming that at least two of them are referring to the wrong field that is being looked up (they cannot all match the Title field).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Kalki]

@WarrenBelz 

So the "Name" entry field and "Ad No." field works when I submit an entry on the published form.

There are no errors shown on the "TIN" and "My No." fields after I corrected the "Update = " formula with your last post. However,  TIN and My No. cells don't populate any data when I submit on the published form.  Could it be that they shouldn't be referencing the Title column as you mentioned?  However I'm not seeing that they all reference the title column.  Could you elaborate?

Thank you.

[WarrenBelz]

@Kalki ,

Yes, that would be the issue - you need to reference the actual field name that each one is looking up in 'Address Log (Map)' - one is probably Title, but the other two will be different fields.

[Kalki]

@WarrenBelz Great! The form is now working once I updated TIN with:

Update = 
{
   Value: DataCardValue2.Selected.TIN,  
   Id:
   LookUp(
      'Address Log (Map)',
      TIN = DataCardValue2.Selected.TIN 
   ).ID
}

and My No. with:

{
   Value: DataCardValue7.Selected.'My No.', 
   Id:
   LookUp(
      'Pursuits Advertisements Log',
      'My No.' = DataCardValue7.Selected.'My No.'
   ).ID
}

However, when I double click a row in my SP list, the form doesn't display the submitted results. It acts like a new form.

What are your thoughts on this? Here are some of the fields that may interest you.

SharePointForm1

Item = If(IsBlank(SharePointIntegration.Selected),First([@'Pursuits Staffing']),SharePointIntegration.Selected)

DefaultMode = FormMode.Edit

[WarrenBelz]

Hi @Kalki ,

That comes down to the DefaultSelectedItems. What are these and also what are the Items of the Combo Boxes.