Hi Team,
I have the following code that will mark my BL column as "True" if certain dates fall in a range and reason = "SCH".
So if there are multiple "True" values in my BL column from the below code I only need to keep one "True" result in my collection that has the least number value in column "AB".
Unsure if I can write in the existing code or I need to do another one.
Any help is greatly appreciated.
Thanks
UpdateIf(
Readings,
true,
{
BL: If
(
DateValue(Readings[@P_READ_DATE]) >= DateValue(Readings[@StartRange]) &&
DateValue(Readings[@P_READ_DATE]) <= DateValue(Readings[@EndRange]) &&
DateValue(Readings[@READ_DATE]) >= DateValue(Readings[@StartRange]) &&
DateValue(Readings[@READ_DATE]) <= DateValue(Readings[@EndRange]) &&
Readings,[@REASON] = "SCH",
"True")
}
);
Hi @christian12
How would BL have more than one value per row, when it appears you only write the string text "True", 1 time, so there isn't a way for it to have more than 1 true. Right now your If, requires that all of them are true, before it writes "True".
Is that what you meant to do?
I guess my point is, you will never have multipe Trues, so your ability to leave more than 1 itsn't possible.
Also, are you sure you didn't want the Dates to be Ors , not Ands? or a combination of ors and ands. (Just checking)
Lastly on this top part, you said
result in my collection that has the least number value in column "AB".
I do not see a column AB, and I have no idea how setting a single true, provides a way to pick a lower number, since it will always be "True" or Blank() or "False" (assuming you add this).
What are the Criteria needed to pick the lowest number in AB and please share an example of data is that in a single row of AB so I can help with that.
And are saying AB is an array of Numbers? and you want to replace all of that with just the lowest number in the array
Also you do not set a value if is = false, you really should if it helps with other queries and delegation.
There is quite a bit of information left, out. Please remember, when you write these asks, to proof read it and ask yourself with this information, no pictures, no data from AB, incorrect details, could you solve it for me? And then hehe take a big drink of water, calm the nerves and re-write and share share share 🙂
If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others
Cheers
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey
Hi @christian12 ,
Do you want to keep only the record in the collection that meet this criterion "Readings[@REASON]="SCH"&&BL=true" and the minimum value of this column "AB"?
Please try this:
ClearCollect(Readings,RemoveIf(Filter(Readings,Readings[@REASON]="SCH"&&BL=true),AB>Min(Readings,AB)))
Hope this helps you!
-----------------------------------------------------------------------------------------------------------------------------------
If my answer solves your problem, please accept it as a solution to help more people stuck on the same problem find it.
Best regards,
Rimmon Li