Solved: Re: Expression: Overwrite value with blank only if...

FaroukM · ‎09-14-2023

I am facing a strange issue when triggering a flow importing from Excel placed in SharePoint, where everything is perfect except of 1 column (DueDate).

- When the DueDate field in Excel contains data then it updates beautifully

- I noticed that when Due Date is empty, then "some" but not all records end up with a strange date 12/10/2026, which is head scratcher for me as this date has no connection to anything

- I changed the Excel sheet just in case there just in case there were formatting issues but the exact same happened

- no background processes are involved and all these records were uploaded/imported with the same parameters and you can see only some have a date showing and some do not

- The proper end result is for all these fields to be "empty"

Expression:

- My issue now is that I want to fix this bug by adding a null value if the excel DueDate is empty, however!!!!!!

- The actual record in the Model Driven App might already have a DueDate and by adding null I will have deleted the original value, which I do not want

- I am trying to find a way to say: If the DueDate in the form already exists and contains data & the DueDate in the Excel is empty then ignore, otherwise if the DueDate in the form is empty then overwrite with null (to avoid these strange dates from appearing)

- Of course would be better the find a way why these dates are inserted without any apparent reason (and where they are coming from)

Thank you

FLMike · ‎09-14-2023

Any chance someone put in a default date?

Also, I would look at the flow run history. You can see right there what data was output to SharePoint. So don't try to guess it, validate first that you see it in the excel file or not in the excel file (for the rows it happened too) and then check out the output of the Update / Create row in SharePoint and it will definitively show you if it was done by your flow or not.

In the Action where you write to SharePoint (or update it). You could write an expression that checks and then inline returns back empty(can't send null), or the date in excel.

If no one else has answered when I wake up Ill write you an example.

Also. you dont need to supply a null. If its empty and you don't provide a value it will be null/empty by default in excel.

Lastly in picture two it looks either like you have a condition check (that I cannot see) or you just wrote those two things to show what you want. But again you only need one and the expression will write blank/null or the datavalue.

But again, go look at the history and see what was written and you can see the INCOMING values (to see if it was in excel or it thinks it was), into the Update Action, and then the output from it.

Thats how you are going to see. Also I would double check if there is some default

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

View solution in original post

FLMike · ‎09-14-2023

Any chance someone put in a default date?

Also, I would look at the flow run history. You can see right there what data was output to SharePoint. So don't try to guess it, validate first that you see it in the excel file or not in the excel file (for the rows it happened too) and then check out the output of the Update / Create row in SharePoint and it will definitively show you if it was done by your flow or not.

In the Action where you write to SharePoint (or update it). You could write an expression that checks and then inline returns back empty(can't send null), or the date in excel.

If no one else has answered when I wake up Ill write you an example.

Also. you dont need to supply a null. If its empty and you don't provide a value it will be null/empty by default in excel.

Lastly in picture two it looks either like you have a condition check (that I cannot see) or you just wrote those two things to show what you want. But again you only need one and the expression will write blank/null or the datavalue.

But again, go look at the history and see what was written and you can see the INCOMING values (to see if it was in excel or it thinks it was), into the Update Action, and then the output from it.

Thats how you are going to see. Also I would double check if there is some default

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

FaroukM · ‎09-14-2023

Thanks a lot for the assistance...

I did check and nowhere a default date can be found
I´m importing from an Excel Sheet in SharePoint and then creating/updating records in Dynamics 365 Model Driven App
The list of records are all the same in the Excel and all have an empty DueDate (I am attaching the pic Flow4 of what is see from "List rows from Excel" step.. Al have DueDate = ""
When I check the Outputs (Get Due Date) then it is also empty (pic Flow5)
I later List Rows of the entity I am adding/Updating and these are the outputs .. The ones with the date shows the 12/10/2026 and the ones without have no DueDate in the Inputs/Outputs (Pics Flow6 and 7)
Please disregard the places where I have the last step adding a null value as i have it now as a stop gap to clear any fields already populated by this peculiar date

I´ve checked any possible default settings, processes or similar automation but none could be found. Hopefully these photos are of any help. Thanks

FaroukM · ‎09-14-2023

Photo of flow7

FaroukM · ‎09-17-2023

Thanks for the help and apologies for wasting anyone´s time. I found out that someone created a background WF in a different solution that updates DueDates based on another entity (validity of a related entity) and then If Contains Data then it will add that date accordingly otherwise it will ignore.