Lookup SP List on filtered document library not working

[aadil-gl]

Hi Friends! Some assistance please 😊

I have a document library with some audio recording which plays fine. The document library already has a lookup column to an SP List and using this SP List I would like to bring in another column. When trying to add the formula it keeps saying invalid argument type.. how can I resolve this? I know its probably because it picks up already existing lookup to the list, though not sure how to bring in the extra columns as part of the lookup.

[RandyHayes]

@aadil-gl 

Your formula does not appear to be correct.  First it is missing the closing parens on it and also, it appears that you are trying to compare to a SortOrder column...is that correct?

[aadil-gl]

Thanks for the quick response @RandyHayes. Oops sorry, forgot to add the closing parens on the image, though the error is still the exact same.

Yep, the column in the SharePoint list is called "SortOrder". I've managed to get to this with no errors but still doesnt bring in any information:

I should say though when collecting the sp list into a collection first and doing the exact first formula to that collection it works as normal. Its just not working against the actual SP List

[RandyHayes]

@aadil-gl 

Yeah, skip the collection anyway!

Tell me what kind of column SortOrder is in your document library.

Also, you have an invalid comparison in your formula.

But, let me know what kind of column SortOrder is and how _guideStep is defined and what you expect to be comparing to InductionContentLookup.Id

[aadil-gl]

Hi @RandyHayes, I think I've managed to get it sorted out (trial and error!). I've added the additional columns to the SP List from the lookup column I am reading. It turns out these do actually show in Power Apps and I've used the Value of it which came it with leading 0s (eg. 2.000). The formula below seems to work (though delegation errors 😥😞

To answer your question though (in the even it may help):

1. The SortOrder column is actually in the SP list (InductionContent  list) and its what I am trying to read using a lookup column (which i've called InductionContentLookup in the document library)

2. Yup, it seems the invalid comparison may always show up when you try to lookup a list from a library that already has a lookup columns referencing the same list. Like trying to do a double lookup

3. SortOrder column (from InductionContent list) is just a number field. The _guidestep is variable that works like a slider (actually from the tutorial screen template) that matches the sort order needed. On each "slide" in the app I am loading an audio file where the current _guidestep matches the sortorder needed from the sp list

Really appreciate your support👍🏼

[RandyHayes]

@aadil-gl 

So, SortOrder is in the InductionContent list that is NOT the document library and is numeric.

Your Document library has a Lookup column in it that references the InductionContent list?  

No, you should not have an invalid comparison in your formula nor should you have delegation warnings in your formula.  The first will cause you problems, so I'd suggest we get the formula right.

For simplicity, I'd recommend only a numeric column in the Doc library to hold the ID of the other list item, or if your data goes the other way, then a numeric column in the list that will hold the ID of the doc library item. A lookup is fine, but there are some extra steps needed to reference it in PowerApps.

So is your intention to get a list of doc library items based on filtering the list by some guidestep variable?

I'm not completely clear on your requirements and design.

[aadil-gl]

Hi @RandyHayes

Yup correct, the Doc Library (Document/Shared Documents) has a lookup to the induction list.

I see! Let me try that. I've always thought having a direct lookup may be better to select the right record etc, though I know lookups come with some caveats.

I brief overview of the solution. Users will be using the Power App to perform an induction which is essentially reading through content and some pictures. The content reads from a SharePoint list and the audio recordings are saved in the doc library. When the screen loads it brings the content and also loads the audio recording for that piece of content (list item) being loaded.

Thanks!

[RandyHayes]

@aadil-gl 

Lookup columns are great if your going to be interacting with the list.  But if you are making a list just a datasource for your PowerApp, the lookup is not needed.

For example, if you have a lookup that references another list, then under the covers, the column contains something like the Title of the foreign list and (not seen) the ID of that record.  This is easier to read if you are in the SharePoint list....that's it!  But in PowerApps, you'll need to deal with that column as a record and correctly supply what it needs.

If you make the "lookup", just a numeric column, then it would just contain the ID of the foreign record...very easy to deal with in PowerApps.  However, going back to the list, it's a little extra work no on that side to look at the other list to find out what record in that foreign list it is referencing. 

So, trade offs to each!

Try it out and see where you get.

[aadil-gl]

@RandyHayes , thank you so much for the information below. Its always been an open question for me whether the proper way of creating the list with lookups etc should be done or not. But your advice really helps!

Since the users will be uploading content and a lookup will not be used, whats the best way to make sure they enter the correct IDs etc?