Skipping Blanks

[Cfis]

Hi All,

Currently building a flow for contract automation and have the majority complete aside from a specific field for a review date.

Some contracts won't have a review date and others will.

Previously had issues with the date format on the "Start Date" so used the below: -

Compose_1:

int(item()?['User - User orig_x002e_ hire date'])

Compose:

addDays('1899-12-30',outputs('Compose_1'),'dd-MM-yyyy')

This works perfectly from testing.  I tried to adapt this to the review date, but as soon as it hits a blank the flow errors.  Current code is: 

Compose_2:

int(item()?['Offer letter - Fixed Term Review Date'])

Compose_3:

addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy')

Any suggestions on how to get it to ignore the blanks if there is a blank on that line?

[v-yetonggu-msft]

Hi ,

Please try put this expression in Compose1 and Compose 2:

if(empty(item()?['User - User orig_x002e_ hire date']),0,int(item()?['User - User orig_x002e_ hire date']))

if(empty(item()?['Offer letter - Fixed Term Review Date']),0,int(item()?['Offer letter - Fixed Term Review Date']))

Best Regards,

Sunshine Gu

[Cfis]

Hi,

So, the Hire Date field is not the issue as that works.  I've taken the below into Compose_2:

if(empty(item()?['Offer letter - Fixed Term Review Date']),0,int(item()?['Offer letter - Fixed Term Review Date']))

This works so thank you! However now when it's blank the date of 30-12-1899 is input because it's now pulling from Compose_3:

addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy')

Some background on this, was struggling originally with the date's inputting onto the word template in the correct format of dd-MM-yyyy and this solution worked.  Presumably in this edge case where there is a blank, it won't work and will need additional refinement?

[Cfis]

I've just tried removing Compose_3 to see the output and in a field that has no review date the output is "0" and in a field that has a date gives me the issue i had before on the start date "45625"

[v-yetonggu-msft]

Hi @Cfis ,

What do you want to know？

Best Regards,

Sunshine Gu

[Cfis]

Well the first part worked but the issue now is that if there is no date in the review date field it puts a date of 30-12-1899 instead of leaving it empty because of the next section of formulas to adapt the numbers into a date field:

addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy')

[v-yetonggu-msft]

Hi @Cfis ,

Please try this expression in Compose 3:

if(equals(addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'),'30-12-1899'),null,addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'))

Best Regards,

Sunshine Gu

[Cfis]

Thanks, tried that but now getting the below error:

InvalidTemplate

Unable to process template language expressions in action 'Compose_3' inputs at line '0' and column '0': 'The template language function 'addDays' expects its second parameter to be an integer. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#adddays for usage details.

[v-yetonggu-msft]

Hi @Cfis ,

Please use this formula in Compose 2:

if(empty(item()?['Offer letter - Fixed Term Review Date']),0,int(item()?['Offer letter - Fixed Term Review Date']))

Best Regards,

Sunshine Gu

[Cfis]

Thanks.  This doesn't error now but if the field contains a date. it's now inputting as "45625" instead of an actual date (in this case it should be 29-11-2024)  This was what the original Compose_3's code corrected

[v-yetonggu-msft]

Hi @Cfis ,

OK,I've found that you always like to change the correct formula according to your ideas, which leads to mistakes, and I have to correct them back and forth, which leads to problems that are not solved for a long time, and I want you to put aside your thoughts for a while and ask you to use this formula in Compose 3 again:

if(equals(addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'),'30-12-1899'),null,addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'))

Best Regards,

Sunshine Gu

[Cfis]

Erm, bit rude.  Compose_3 has the exact string that you previously sent over and i've not changed it at all yet it results in the issue mentioned above.

[v-yetonggu-msft]

Hi @Cfis ,

How about this?

Could you please send me a screenshot of your flow?

I want to know what formulas you used in Compose 2 and Compose 3 respectively, and whether you used the list rows present in a table control?

Thanks.

Best Regards,

Sunshine Gu

[Cfis]

Compose_1 and Compose are for the start date field which work perfectly.

Compose_2 and Compose_3 are the problematic ones for the review date

[v-yetonggu-msft]

Hi @Cfis ,

Please try this expression in Compose 2:

if(empty(item()?['Offer letter - Fixed Term Review Date']),0,int(item()?['Offer letter - Fixed Term Review Date']))

And please try this expression in Compose 3:

if(equals(addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'),'30-12-1899'),null,addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'))

And I did a test for your reference.

In my scenario:
My Excel Table:

Flow Run History:

Flow Configuration:

Best Regards,

Sunshine Gu

[Cfis]

Same issue, example output on one that has no date in the review field works fine:

Example of one with a date in the review field:

[v-yetonggu-msft]

Hi @Cfis ,

Could you please show me your formula in Compose 3?

Many thanks.

Best Regards,

Sunshine Gu

[Cfis]

Yup, it's the code you sent:

[v-yetonggu-msft]

Hi @Cfis ，

The 'Offer letter - Fixed Term Review Date' column in your excel table needs to be formatted:

And please try these two expressions in Compose2 and Compose3 respectively.

Compose_2:
if(empty(item()?['Offer letter - Fixed Term Review Date']),0,int(item()?['Offer letter - Fixed Term Review Date']))
Compose_3:
if(equals(outputs('Compose_2'),0),null,addDays('1899-12-30',outputs('Compose_2'),'dd-MM-yyyy'))

Best Regards,

Sunshine Gu

[Cfis]

The excel sheet cannot be changed as the process needs to be automated with minimal touch points and is based on a system output into Excel.  The field is currently formatted as a short date but still causes the issue.