Default selected items is not loading by lookup ID

[tonykiefer]

I have a combobox that loads available items for selection from a SQL View...this table has about 600 records

I have another table(BOMDetails) that looks up the current selected item by id.

The Combobox "DefaultSelectedItems" property is set this way...

LookUp(BOM_View_Details,BoM_ID=ThisItem.PLM_BOM_ID),

This isn't working.  I believe the reason is no shared ID.  No FK.

So my idea was to create a variable and set it like so...

Set(varMyPLMBomID,LookUp(BOMDetails,BOMID=currentBOM.BOMID,PLM_BOM_ID))

and set the Combobox DefaultSelectedItems property this way...

Lookup(BOM_View_Details, BoM_ID=varMyPLMBomID) but this doesn't work either.

I created a label on the page and set it too varMyPLMBomID and it does not appear the variable is getting set.

Is there a better way for me to achieve the overall goal of populating the list via the view and setting the defaultselecteditem property to the previously saved value from the BOMDetails table?

[GarethPrisk · Accepted solution]

The original formula you have for DefaultSelectedItems should be sufficient.

LookUp(BOM_View_Details,BoM_ID=ThisItem.PLM_BOM_ID)

As per your examples, that appears valid and should resolve to the first matching record.

A few things to confirm

• Since you're using ThisItem. - it's presumably a ComboBox in a Gallery
• Confirm with a label, that ThisItem.PLM_BOM_ID is actually populated in the app
  • Power Apps has a feature where it only brings in data for fields being used - to increase performance
  • In some instances, it doesn't realize a field is being used when its only in a formula
  • Ensure this is there
• Those two columns are identical data types?
• It won't matter if you have an explicit FK set in SQL, the column types just need to (mostly) match and the data has to resolve correctly
• You didn't mention it but the ComboBox Items property is based on BOM_View_Details, or some abstraction if it, right?

[tonykiefer]

Hi Gareth,

Thank you for the response.  I'm certainly having some trouble with this.  To answer your questions...

ThisItem.PLM_BOM_ID is not populating in a label text property.

The Bom_Id is nvarchar(115) and the and the PLM_BOM_ID is nvarchar(50).

The combobox items are...

LookUp(BOM_View_Details,BoM_ID=ThisItem.PLM_BOM_ID)

Let me show you some workflow...

The page previous to the one in question is the BOM Gallery

The Add BOM Action HTML LINK is set...Set(currentBOM, ThisItem); 

This link send me to the problem page...

The BOM Ref Combobox is set as....

The textbox and the label are both set...

LookUp(BOMDetails,BOMID=currentBOM.BOMID,PLM_BOM_ID)

I don't need those fields.  They are only for testing

In the combobox...sometimes the defaultselectedItem actual appears but then it disappears.

I hope this helps

[GarethPrisk]

Did you add a label with ThisItem.PLM_BOM_ID to your gallery? Try that, and then save and re-open the studio. Does it populate then? If so, this may help with the irregularity of that field being pulled into the app. You can always hide the label, to keep the data forced into it (without user seeing it).

Let me know if that helps.

[tonykiefer · Accepted solution]

Hi Gareth...thank you for the insight.  I thought I was going crazy and I couldn't figure out why the default selected items wouldn't work.  I knew the syntax was right and you confirmed it.  I discovered the problem.  While comparing the data in the two table columns that I'm comparing/searching is slightly different.  The PLM_BOM_ID should be the same and the BoM_Id...however it is not.  I need to rewrite the view to ensure there is the same amount of whitespace between each substring as the BOM_Details table.  

That's why the default selected items wasn't populating.  The difference in the whitespaces between the substrings didn't offer and exact match.

That's a real Palm to the Forehead moment.

So frustrating.  Thank you for your help!

[tonykiefer]

@GarethPrisk I corrected the issue and when the page load the default selected item appears in the combo box but then disappears.  Do you have any idea why that happens?