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

(Miranda Lubarsky) #1

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.


Can you try it like this?

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

(Aleksi Alkio) #3

Another formula could be…

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

(Steven Coile) #4

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 Lubarsky) #5

@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!!!