Mabel @v-yamao-msft & Barry @v-bacao-msft,
Thank you for your collective help on the above over several posts to this forum.
Let’s reaffirm what we need to with an actual example for one supplier as below:
Existing Expiry | Days to Add | New Expiry |
| | | |
31/03/2017 | 365 | 31/03/2018 |
31/03/2018 | 365 | 31/03/2019 |
31/03/2019 | 366 | 31/03/2020 |
31/03/2020 | 365 | 31/03/2021 |
There are two ways I have been trying to achieve this without much success.
USING FLOW
I know the compose functions above are working well and I generally understand what they are doing. The issue comes with trying to use them in the rest of the flow to set the new expiry when the company send in their new certificate in. The easiest thing would be to get the company to rename their certificate and then use part of the file name (date) and then write that to the list. I cannot rely on them renaming the file in this specific way as this will be too complicated to explain in an email and to tell them why I am needing this.
So, the best way is to accept what we are given and work with that. So, with flow1 we send out request for new certificate and they reply with the new one attached. This is the trigger for flow2 (this flow). We then check them against the SP list (got this working and to ensure we write the data against the correct line item). This is where I believe I need to be using the existing expiry date and undertaking a test by adding 12 months to it. But this where the complexity starts and using the various compose functions to undertake this test defeats me.
USING SP LIST CALCULATION
The other option is to look at the existing expiry column in the SP list and then have another column called new expiry. The new expiry shall be a calculated column and shall add the correct amount of days to achieve the same results of the above. The issue is I need to have a calculation like the below expression to achieve this that works in a SP column:
if(or(equals(mod(outputs('Convert_Year_to_Number'),400),0),and(equals(mod(float(outputs('Get_the_Year_of_a_Date')),4),0),not(equals(mod(outputs('Convert_Year_to_Number'),100),0)))),'366','365')We may need to achieve this via multiple columns and I am happy to do this if required.
So which way do I go the flow route to add the correct amount of days or the SP list way. Whichever way I do go. The main aim is to ensure the correct amount of days are added to the existing expiry.
Perhaps I am not thinking about this as logically as I should, and I may need to combine both methods to get one result. Or I need to be thinking about a completly different way which is far simpler.
I am going to keep the "date testing" open and make all future comments on this matter against the “leap years” post. When all is sorted and running I shall mark the “date testing” as solved. I am sorry for the cross posting.
Regards,
Andrew
