So I have a variable that works
Set(
varBuildingDailyCount,
Filter(
collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value
).NumberPerday
)
This works to bring back the value of number per day. My issue is that sometimes that number does not exist and I need to set the value to zero. If I alter the formula with an if, I cannot seem to set the table value to zero if blank.
Set(varDailyCount, If(IsBlank(Filter(
collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value
).NumberPerday
),0,
Filter(
collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value
).NumberPerday
))
Any and all help would be appreciated!
Solved! Go to Solution.
To be clear, in your set formula, you are setting your variable to a table, not a value. If you want just a value, then you need to utilize the LookUp function and not the Filter function.
ex. with LookUp and also Coalesce to provide the 0 value if blank.
Set(
varBuildingDailyCount,
Coalesce(
LookUp(collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value,
NumberPerday
),
0
)
)
If it truly is a Table that you want in your variable, then you can implement this:
Set(
varBuildingDailyCount,
ForAll(
Filter(collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value
).NumberPerday,
{NumberPerday: Coalesce(NumberPerday, 0)}
)
)
I hope this is helpful for you.
To be clear, in your set formula, you are setting your variable to a table, not a value. If you want just a value, then you need to utilize the LookUp function and not the Filter function.
ex. with LookUp and also Coalesce to provide the 0 value if blank.
Set(
varBuildingDailyCount,
Coalesce(
LookUp(collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value,
NumberPerday
),
0
)
)
If it truly is a Table that you want in your variable, then you can implement this:
Set(
varBuildingDailyCount,
ForAll(
Filter(collectionDailyLog,
FieldDate = varCurrentDate && Building = varBuilding.Value
).NumberPerday,
{NumberPerday: Coalesce(NumberPerday, 0)}
)
)
I hope this is helpful for you.
User | Count |
---|---|
164 | |
90 | |
73 | |
64 | |
62 |
User | Count |
---|---|
211 | |
153 | |
96 | |
88 | |
66 |