My current formula:
LOOKUP( [Date] , โMolding Manpowerโ , Production Date , Total People)
It returns the total people working in a workcell on a given date from my Molding Manpower table.
The problem is I have multiple shifts and operations. I need to match the โDateโ, โShiftโ, and โMixer Numberโ and return the Total People.
I feel like in excel I would use filter() or multiple nested if() statements.
Solved! Go to Solution.
Solution:
ANY(
SELECT(
Molding Manpower[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])),
FALSE
)
)
Thanks for pointing me in the right direction guys!
@Kyle_Richardson - sounds like you just need some help with expression syntax. Appsheet supports both Filter and nested IF statements. You can find syntax help at Appsheet.com/support.
I certainly am having syntax issues in that I know how to use each of these properly on their own and I cannot figure out how to use them together to get what I need.
My formula now:
FILTER(
โMolding Manpowerโ[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])
)
)
This produces a list. I just need the single value of [Total People] in a row where it meets my 3 criteria.
As @Mike suggested, you should probably read the documentation. For instance, your FILTER() syntax is wrong: FILTER(โMolding Manpowerโ[Total People], ...)
should not include [Total People]
there. If you read about LOOKUP(), youโll read that โLOOKUP() is effectively a wrapper to the SELECT() function.โ If you look at SELECT(), youโll see itโs what you want.
Solution:
ANY(
SELECT(
Molding Manpower[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])),
FALSE
)
)
Thanks for pointing me in the right direction guys!
Perfect!
thanks @Kyle_Richardson
I see the fomula ok but it seem very slow syncing.
A virtual column with an App formula expression that uses FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), or SELECT() is very likely to hurt sync time.
See also:
User | Count |
---|---|
42 | |
33 | |
24 | |
23 | |
14 |