Getting value from lookup of a lookup on SharePoint list

[valex]

First of all I want to thank all the contributors here, I've learned more from your advices than from the official guides. However, as though I was thoroughly searching for the solution for my problem, I haven't found the solution on these pages for the last two days, so I was forced to open a new topic.

Namely, I am building a custom form application based on one SharePoint list, "Expences". There, I have a lookup column called "Budget line" that is a lookup column, getting values from "Title" column of the another list, "Budget line", and another lookup column called "Project", getting values from "Title" column of the list "Project". The "Budget line" list also has the "Project" column, getting the same data. Graphic representation of the relationships is in the attached image below.

What I want to achieve is when the Project is picked via combo box in PowerApps form, that the another combo box with Budget lines offers only items that are related to the chosen project (single choice in both cases). OK, easy, used "Depends on..." on Properties tab of the combo, and I got the following formula in its "Items" field

Filter('Budget line', Project:ID = ChosenProjectCombo.Selected.Id)

However, I got an error, showing the red tilde under the equation sign (you can see that in the image below - I have translated the list/column names in the text of this post for you to handle it with more ease) . I have checked the formula on existing posts on this forum and it seems that is valid.

What I doubt the origin of the problem is that when I unroll the data source "Budget line" to see its contents, I see only [Record] as lookup column data, not the values. Is there a solution to "dig down" through the data source to retrieve the values of the record?

You're my only help, thank you in advance!

[WarrenBelz]

Hi @valex ,

I am flying a bit blind without seeing your data structure properly, but for a start try (you also had an extra bracket I have removed)

Filter(
   'Budzetske pozicije', 
   Projekat_x003a_ID = Projekatlzbor.Selected.Id.Value
).Title

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.

[valex]

Thank you @WarrenBelz - I've tried already with the variations of picking column name behind the filter and not, but the result is the same, still red on equation and an error in formula. The screenshot with extra bracket was in one of hundred variations, thank you. What bothers me is that the formula was autogenerated by PowerApps "Depends on...", and it is valid when you use it between fields that have regular datasources (not nested lookup like in this case)...

[WarrenBelz]

Hi @valex ,

This should not be difficult to track down - you have two values - Projekat_x003a_ID in your list 'Budzetske pozicije' and you have a Combo Box / Drop-down called Projekatlzbor. I have realised that Projekat_x003a_ID is probably a Numeric field, so that will change my answer below and that this field is also a Record according to your screenshot. So try this

Filter(
   'Budzetske pozicije', 
   Projekat_x003a_ID.Value = Value(Projekatlzbor.Selected.Id)
).Title

Note that Projekat_x003a_ID.Value may be different depending on the record structure.

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.

[valex]

That seems more logical then my formula, @WarrenBelz, thank you, and now there is no error. However, the dropdown produces no choices in production 😞

I have created plain numerical columns in my parent list and copied the ID's of Project and Budget line to them, then used the formula from my initial post here (with .Title at the end) and it worked. So definitely there is something strange happening with lookup fields...

[WarrenBelz]

@valex ,

Did you mention a lookup column? Try this

Filter(
   'Budzetske pozicije', 
   Projekat_x003a_ID.Id = Value(Projekatlzbor.Selected.Id)
).Title

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.

[v-xida-msft · Accepted solution]

Hi @valex ,

Is the "Project:ID" column a LookUp type in your 'Budget line' SP List?

Does the the "Project:ID" column in your 'Budget line' SP List reference values from the "ID" column in Project List?

Regarding the formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please try the following workaround:

Set the Items property of the 'Budget line' ComboBox in your Edit form to following to following:

Filter(
       Choices(Expenses.'Budget line'),
       Value in Filter(
                       'Budget line',
                       Project:ID.Value = ChosenProjectCombo.Selected.Id
                ).Title
)

or

Filter(
       Choices(Expenses.'Budget line'),
       Value in Filter(
                       'Budget line',
                        Project.Value = ChosenProjectCombo.Selected.Value  // use .Value rather than .Id
                ).Title
)

Please consider try above solution, check if it could help in your scenario.

Note: Please replace the data source name, field name in above formula with actual SP List name and Column names from your own side. The above formula is different from the solution @WarrenBelz provided. 

Regards,

[valex]

@v-xida-msft, @WarrenBelz, thanks to both of you, guys - now this works like a charm and you saved my day 🙂 I thought that the problem was in lookup nesting and that I have to retrieve the parent of a parent like a table and then to filter it (showing items as [Record] and not values confused me), but it was all in proper syntax.

[Vuzeo]

Dear @valex,

I have come across this issue too and your topic helped me understand my issue, although I do have to ask šta znači "budžetske pozicije"? 🙂

Have a great day!

-Ajdin