Forum Discussion
Solved
Calculated Column Using Number Data to Return Different Values
Hi, I am trying to use a calculated column (that's using data from another calculated column) to return a string based on some simple conditions.
Background: I have a powerapp that collects data in a sharepoint list. This app collects the date and time whenever a record is entered, and I use a calculated column (Time) to pull the time out in a simple four number format (in 24 hour format) so I can compare the values as integers. (For example, 10/22/2019 at 8:30 A.M. becomes 0830 and 10/22/2019 at 3:45 becomes 1545) I do this so I can then use an if statement in a new calculated column (ShiftCode) that calculates which shift the data was entered during so I can slice the data more meaningfully in PowerBI. The if statement is as follows:
=IF(Time>=700 & Time<1600,"Shift 1",IF(Time>=1600 & Time<2300,"Shift 2", Shift 3"))
Logic: If the time is within the hours for first shift, return shift 1. If it is within the hours for second shift, return shift 2. For all else return shift 3 since that is the only other possibility.
For whatever reason, this always returns "Shift 3". Not sure why, and was wondering if there was anything I am doing wrong. I've looked over the documentation for the syntax but can't seem to find out why this is not returning the correct value.
Anything you can see that I've messed up and can change to solve the issue?
- Gotcha. I would create a new column in that case, use flow to process or loop through the list and update the column to the appropriate value, then add the logic to the button to record the value in the list going forward so you don't have to maintain a flow and powerapp and it'll make more sense if anyone needs to go edit it later.
Flow would be started using flow button (You can run it manually when ready to go from the flow editor)
Throw in a getitems action to get all items in list.
Update Item action using the getitems ID this will add it to a loop.
You can then put a condition in front of it to handle the shifts based on the calculated date, or if your feeling randy figure out how to use a flow expression and do it in a single step. This is basically like calculated column nesting if's to set the value in a single action instead of using conditions.
You could basically do this flow for a single item, once you get it working, then go back and and in the get items part and do the loop through.
Another tip, don't forget to go in and set paging or pagination? Something like that so it allows to pull more than 100 records in the loop. It's in the settings of the getItems action i believe.
10 Replies
- I’m not sure if it will trigger delegation warning or not but you can try wrapping your column on Value(column) function to convert it from text assuming that was the column output to a value so it can compare it properly.
ChrisWebbTechThanks for the reply! I tried this but it still returns shift 3 every time.
- Oh I was thinking you were doing this in PowerApps. I don't think you can reference a calculated column in another one since the value will probably be determined prior to it being saved, which means the value will be null. Go edit the item afterwards with something, and I bet it will then update with the proper value? That's how you can tell if that's the case.
