Converting a number column by lookup from another table

[Lefty]

Hi

Struggling to see if this is possible.

I have a canvas app linked to SQL consisting of the following 4 relational tables:

Table1:

JCode

JDescription 

PRIndex - Number column

Table2

PRIndex - Number column

PIndex - Number column

RIndex - Number column

Table3

PIndex - Number column

PName 

Table4

RIndex - Number column

RName

When a user comes in to my PowerApps, they should be able to select an item from JDescription ComboBox(using data from Table1), which should give them a list of Profiles associated to the value selected. My Issue is PRIndex in Table1(number column) is linked to Table2 which only has number columns linking Table3 (PIndex ) and Table4 (RIndex).

I cannot see how I can display to an end user what the number value represents, it seems I need to do a lookup inside a lookup?

Any suggestions please?

I also need to display items linked to the selected value a user selects from JDescription ComboBox in the 2nd ComboBox pulled from Table3 PName - but Table3 and Table 1 are not directly linked.

Thanks

[RandyHayes]

@Lefty 

Yes, your formula would look something like this:

LookUp(Table3, PIndex = LookUp(Table2, PRIndex = yourCombobox.Selected.PRIndex, PIndex), PName)

This would return the PName from Table 3 associated with the combobox selection.

I hope this is helpful for you.

[Lefty]

Hi @RandyHayes 

Thanks for your response. It's good to know you can do a lookup inside a lookup. If you could help with the syntax please as I may not have explained things properly.

The first part of the issue is I have a gallery and I am looking to display the text value of PRIndex in the Label so on that I did this but I get a delegation warning, and no data displayed in the gallery label:

LookUp(Table3, PIndex = LookUp(Table2,PRIndex = PRIndex, PName)

With regards to Selecting a value when I select PName from a combobox for the RName ComboBox I have attempted the following but i'm not quiet there, on the items property of RName comboBox I have the following:

LookUp(Table3, RIndex = LookUp(Table2, PRIndex = CbxProfile.Selected,RIndex), RName)

If on the above I CbxProfile.Selected. The only option I get is PName

I'm clearly not doing both right

[RandyHayes]

@Lefty 

First...you will get a delegation warning because the double LookUp's will not be delegable.  If you have more than your record limit (2000 max), then alternate means are needed.

You are mentioning now that you have a Gallery.  At first it was just a combobox, so I am assuming this is something else.  If so, what is the Items property of your gallery?

Your second formula for the combobox Items is using a lookup...a lookup is only going to give you 1 record.  So, what are you trying to achieve on that?

For that Items property you would want to Filter and not just Lookup one record:

Filter(Table3, RIndex = LookUp(Table2, PRIndex = CbxProfile.Selected,RIndex))

[Lefty]

Hi @RandyHayes 

I have more than 2000 records, so is this option worth exploring or is there something you may suggest?

Sorry, I had not mentioned gallery in my initial question. I have 2 questions, 1 related to a gallery and another related to values being display when a selection in a comboBox is made:

1- My Gallery Items property is pointing at Table1:

SortByColumns(
    Search(
        Table1,
        TextInpSearch_3.Text,
        "JCode",
        "JDescription"        
    )
    ,
    "ID",
    Descending
)

Rather than show the number value for PRIndex (which exists in Table1) I want to be able to show the text value,  but the tables (Table1 & Table3) are not directly linked. Table2 contains the link between Table1 and Table3 i.e. PRIndex is found in Table1 and Table2, and I need PName associated with PRIndex, PName is found is Table3.

I want to display the value of PName in this gallery column in place of a number value (this would be single items linked to each record). I'm not sure if I have explained this as its a little confusing.

2-
I have a comboBox1, when a user selects a value from Table1, 'JDescription', they should only see items associated with that value in comboBox2 (this could be 10 items, 5 items etc. depending on the selection in comboBox1) , the linking of the tables again is PRIndex, found in Table1 and Table2, but the text value (PName) is found in Table3.

Table1 contains PRIndex, Table2 contains PRIndex & PIndex, Table3 contains PIndex and PName. I would like to display ALL the values linked to PName, which should look at the selection name in JDescription and possibly use that to bind the values... not sure if I have explained this well?

I've not worked with multiple tables and I haven't set up the data structure, so it's proving very difficult to get to grips with it all. Any further help would be highly appreciated