Referenced Rows Error

[McKenna_Killion]

Hello, 

I'm struggling with the formula for my reference rows in this action. I keep receiving the error that it "does not match the expected type List or its expected type details."

Here's the two actions:

the formula is: 

UNIQUE(
LIST(
IF(CONTAINS([Phase], "Cement Racing Lanes"), FILTER("Consumable Lists", [Cement Racing Lanes]=TRUE), LIST()),
IF(CONTAINS([Phase], "Cement Waterline Tile"), FILTER("Consumable Lists", [Cement Waterline Tile]=TRUE), LIST())))

I also first tried it with SELECT() which produced the same result. 

UNIQUE(
LIST(
IF(CONTAINS([Phase], "Cement Racing Lanes"), SELECT(Consumable Lists[Item ID], [Cement Racing Lanes]=TRUE), LIST()), 
IF(CONTAINS([Phase], "Cement Waterline Tile"), SELECT(Consumable Lists[Item ID], [Cement Waterline Tile]=TRUE), LIST())))

The formula itself does not produce an error within the expression assistant, though the UNIQUE() function doesn't seem to be working. 

Grateful for any help with this as I still have a lot left to figure out with this app 😅

[Markus_Malessa]

Try the following:

IFS(CONTAINS([Phase], "Cement Racing Lanes"), SELECT(Consumable Lists[Item ID], [Cement Racing Lanes]=TRUE, TRUE), 
CONTAINS([Phase], "Cement Waterline Tile"), SELECT(Consumable Lists[Item ID], [Cement Waterline Tile]=TRUE, TRUE))

[McKenna_Killion]

Thanks for the suggestion. Unfortunately, that doesn't work. IFS() stop processing after the first true value. The column for [Phase] is an enumlist and both of these options may be selected. If so, I want to include the rows for both of them (but they also have rows in common, which is why I'm trying to use the UNIQUE() function to remove duplicates. 

This also didn't make a difference in removing the error about not matching the expected type List.

[Markus_Malessa]

I see. The following might work instead:

LIST(IFS(AND(IN("Cement Racing Lanes",[Phase]),IN("Cement Waterline Tile",[Phase])),SELECT(Consumable Lists[Item ID],OR([Cement Racing Lanes],[Cement Waterline Tile]), TRUE),IN("Cement Racing Lanes",[Phase]),SELECT(Consumable Lists[Item ID],[Cement Racing Lanes],TRUE),IN("Cement Waterline Tile",[Phase]),SELECT(Consumable Lists[Item ID],[Cement Waterline Tile],TRUE)))

[McKenna_Killion]

Half yes! Using the IFS() like that is returning the correct rows now in the test, but it's still giving the same error message for the action. I'm just not sure what's wrong with the list/its details. 

I confirmed that the Item ID is the key for the table. 

[Markus_Malessa]

Try removing the LIST() part of the formula. So remove LIST( and remove the last ). I'm hoping that will work. If not I got one other solution to try.

[McKenna_Killion]

That worked!! Thank you!

However, I've just remembered that I need to scale it. There's 12 phases so a lot of possible combinations depending on the scope of work for each job. That's why I was trying to make a list out of IF() and then remove any duplicates.

I'll keep working on it of course, but just wondering if you had any thoughts?  

[Markus_Malessa]

I have a suggestion for possibly restructuring your data. It appears that your Consumable List table might have true/false columns for these categories? Rather than true false columns can it just be an EnumList also? Then you could alter your other statement to just:

SELECT(Consumable Lists[Item ID], [Phase] = [List Categories], TRUE)

[McKenna_Killion]

Very good point! It's the way I put the data together, but I didn't think about simplifying it further. I'll try that out. Thank you

[Markus_Malessa]

One thing to note is that the IN() function only works for checking if a singular item is in a list of items. It does not check if multiple items are in a list of items, so there might be some additional finagling that needs to be done to make this work. But doing 12 possible combinations does become difficult.

[Markus_Malessa]

What might work (emphasis on might) is to still make your 12? true/false columns an enumlist instead and then try this for your reference rows:

SELECT(Consumable Lists[Item ID], [Phase] = INTERSECT([Phase], [List Categories]), TRUE)

[McKenna_Killion]

Why did you put [List Categories]? I don't have a column of that name so I'm not sure what you mean. 

[Markus_Malessa]

It's a placeholder for whatever you call that new column.

[McKenna_Killion]

Ah, okay. That did not work, unfortunately. I've tried a bunch of small changes and different things and haven't been able to make any progress.

I found another way to get the correct reference rows that I need using virtual columns to individually create and then combine the lists, but I still get the same error on the action. 

[hmh740070]

It sounds like you're working on a project with 12 phases and need to generate a list of possible combinations based on the scope of work for each job. Using IF() statements to create the list and then removing duplicates is a good approach. You might also consider using combinations or permutations functions depending on the specific requirements of your project. If you provide more details, I can offer more specific advice.

[McKenna_Killion]

So I'm creating an app to output the items and amount of consumables materials required for our pool renovation projects. The first part that I'm working on here is just to create the list of items based on the phases that a crew supervisor will be loaded up with before doing that work. Some of the items are used for just one phase, some items like buckets are used in almost every phase. 

I have one table which is used as a form to select the job and phase for which they want to load material. The 2nd table, Consumable List, is then used to decide which materials (and how much) are needed for that job and phase(s). 

The 3rd table is the Consumables Needed, which populates from the List based on what was entered in the Form. 

 As for the combination of phases, it's actually 2 lists of 9 that are the possible combinations of phases for each project we do:

Coping Prep	Coping Prep
Coping Install	Coping Install
Hydroblast	Hydroblast
IG	Plaster
Surface Prep	Surface Prep
TD Spray	TD Spray
TD Stain	TD Stain
IG Waterline Tile	Cement Waterline Tile
IG Racing Lanes	Cement Racing Lanes

[McKenna_Killion · Accepted solution]

I have gotten it to work. I do not know why this works...but I'm just happy it does. 

I made a virtual column on my Form table for all of the phases. The key to getting rid of the action error message seemed to be using TEXT() 

LIST(
TEXT(IF(CONTAINS([Phase], "Cement Waterline tile"), FILTER("Consumable Lists", IN("Cement Waterline tile", [Phase])), LIST())), 
TEXT(IF(CONTAINS([Phase], "Cement Racing Lanes"), FILTER("Consumable Lists", IN("Cement Racing Lanes", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "Coping Prep"), FILTER("Consumable Lists", IN("Coping Prep", [Phase])), LIST())), 
TEXT(IF(CONTAINS([Phase], "Coping Install"), FILTER("Consumable Lists", IN("Coping Install", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "Hydroblast"), FILTER("Consumable Lists", IN("Hydroblast", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "IG"), FILTER("Consumable Lists", IN("IG", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "Plaster"), FILTER("Consumable Lists", IN("Plaster", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "Surface Prep"), FILTER("Consumable Lists", IN("Surface Prep", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "TD Spray"), FILTER("Consumable Lists", IN("TD Spray", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "TD Stain"), FILTER("Consumable Lists", IN("TD Stain", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "IG Waterline Tile"), FILTER("Consumable Lists", IN("IG Waterline Tile", [Phase])), LIST())),
TEXT(IF(CONTAINS([Phase], "IG Racing Lanes"), FILTER("Consumable Lists", IN("IG Racing Lanes", [Phase])), LIST()))
)

Then the reference rows in the action is simple:

[Suvrutt_Gurjar]

Hi @McKenna_Killion ,

Nice to know that you got a solution and thank you very much for posting it because any future reader of the thread would know the outcome and solution.

At this stage , as a fellow community member,  may I also request ( 🙏) you if possible to give some applause , a few likes to @Markus_Malessa  and @hmh740070 .

Especially @Markus_Malessa has been with you on this a bit cryptic requirement through a long exchange of posts. I am sure Markus has invested his time into it and I presume his replies have helped you in some way at least if not the exact solution.

Many community members responding to queries do it voluntarily and I am sure a bit of encouragement will go a long way in making this great community still greater. 

Thank you  and best wishes with your app creation.

[McKenna_Killion]

Yes, of course, I'm very thankful to @Markus_Malessa for all of the suggestions and ideas. It's hard to keep going on a problem alone once you get stuck. Thank you!

[Suvrutt_Gurjar]

Thank you very much @McKenna_Killion  for your revert and best wishes with your app creation. Appreciate.