Hey all, So I'm in the Column structure and ...

[Miranda_Lubarsk]

Hey all,

So I’m in the Column structure and trying to get the Price of plants based on their Plant Size in another table.

I am using this expression in a virtual column in the app formula field:

=ANY(SELECT(Design Numbers for 2018[Price], Design Numbers for 2018[Tray Size] = [_THISROW].[Plant Size (1)]))

It verifies & saves, etc. all good, seemingly.

BUT, it is not showing up in the form or table when I go to fill out the form for this table, entitled Timesheets.

I have already double checked that nothing is hidden or filtered out in the table & form view, the column structure, the table, etc.

The weird thing is that when I go to the Table View and scroll down to look at the columns, it shows up.

But when I go into the form, it does not show, even though I have put no filters on the form and don’t have any slices or anything like that going on.

I have also made sure that the table I am referencing doesn’t have any hidden stuff going on, or filters/security issues, etc.

Running out of theories…anyone have any ideas?

Starting to think that maybe something weird is happening with the expression?

I’m not sure.

[Stan]

Can you try it like this?

=ANY(SELECT(Design Numbers for 2018[Price], [Tray Size] = [_THISROW].[Plant Size (1)]))

[Aleksi_Alkio]

Another formula could be…

LOOKUP([_THISROW].[Plant Size (1)],Design Numbers for 2018,Tray Size,Price)

[Steven_Coile]

You incorrectly include the Design Numbers for 2018 table name in your original expression:

=ANY(SELECT(Design Numbers for 2018[Price], Design Numbers for 2018[Tray Size] = [_THISROW].[Plant Size (1)]))

It should be:

=ANY(SELECT(Design Numbers for 2018[Price], [Tray Size] = [_THISROW].[Plant Size (1)]))

Note that the result is indeterminate if either [Tray Size] or [_THISROW].[Plant Size (1)] are blank.

[Miranda_Lubarsk]

@Stan @Aleksi_Alkio @Steven_Coile thanks guys.

Actually had to do a work around because I couldn’t get the de-ref to un-link itself between plant ID and plant size, so i created a new table with pricing and then ended up using a SUM expression with a whole bunch of new refs to the new table.

Not happy because this means i will have to update pricing in two places, but it works for now.

Thanks for all your help! You guys are expression wizards, I bow down to your expertise!

Almost at the end of the road with this app and couldnt have gotten anywhere without your help! Many many thanks!!!