Forum Discussion
Need help with multiple Nested If Columns
Hi
I'm hoping someone might be able to help me with a problem. I have an MS form using power automate to populate my SharePoint List. I have 3 inter-dependent columns:
- Choice Column with 4 choices
- Calculated column using Nested IF statements to populate column 2 dependent on the Choice (Column 1) (This is a Hidden column)
- Calculated Date Column using Nested IF statements to calculate the date dependent on Column 2.
Column 1 (Choice): 'Previous Extension (1 of 1)':
7 Day Extension Request: Previous Self-Certification
Additional Postponement for Exam or TCA
7 Day Extension Request: No Previous Extension
14 Day Extension Request: No Previous Extension
Column 2 (Calculated): 'Requested Days (1 of 1)':
=IF([Previous Extension (1 of 1)]="7 Day Extension Request: Previous Self-Certification","7",IF([Previous Extension (1 of 1)]="7 Day Extension Request: No Previous Extension","7",IF([Previous Extension (1 of 1)]="14 Day Extension Request: No Previous Extension","14",IF([Previous Extension (1 of 1)]="Additional Postponement for Exam or TCA","",""))))
Column 3 (Calculated): 'Requested Extension Date (1 of 1)'
=IF(ISBLANK([Requested Days (1 of 1)]),"",IF(ISNUMBER([Requested Days (1 of 1)]),[Submission Date]+[Requested Days (1 of 1)],""))
BTW, the reason for the (1 of 1) is because the form has 5 options with equivalent branching. I have previously tested the flow with these naming conventions and it worked, and currently the flow runs but will not pull the data from the choice field and so the other fields won't populate. Any suggestions on where I might be going wrong would be appreciated.
Many thanks
4 Replies
So, is the issue with Power automate flow not pulling the data from MS form response and not adding it in SharePoint list columns? If yes, can you share details about the flow you are using?
Or is there any issue/error with the calculated columns?
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Hi, thank you for responding earlier but I've fixed it. My IF(ISBLANK statement was incorrect. I've changed it to this and it works perfectly:
=IF(ISBLANK([Requested Days (1 of 1)]),"", DATE(YEAR([Submission Date]), MONTH([Submission Date]), DAY([Submission Date])+ ([Requested Days (1 of 1)])))
Thanks again.
Hi, the flow is triggered by 'When a new response is submitted'. The flow itself is running and pulls every other piece of data across, except this choice column. Yesterday when I was running some tests, I didn't have my Nested If statement in Column 3 and the flow worked fine, pulled the choice column data with no issues but the column didn't populate because I had the wrong calculation in there. As soon as I changed the calculation to what I posted earlier, it stopped pulling the data.
If I look at the flow run, it's pulling the data from the form via 'Get Response Details',:
but is not populating that data into 'Create Item'. The outputs say 'null' for those fields:
Because there is branching in the form I am using 'Compose' and the following outputs within the flow to negate the flow not working when faced with a compulsory field and no data due to branching:
if(empty(outputs('Previous15')),null,outputs('Previous15')).As I said, it populated yesterday but the calculated column wasn't correct so it was populating columns it shouldn't. As soon as I change the Nested IF it stopped pulling the data but still runs successfully.- UPDATE.
I went back to my flow and re-entered the fields and re-ran the test and the data from the choice question is now coming across so it's not the flow.
