Flow based on Year, ID Number, and Action Type from sharepoint list

[lisbonc1987]

Hello,

I am very new to power automate and power apps. I created a list with hopes of autogenerating a serial number based on a choice from "Action type" choice column and "Fiscal Year" choice column. Based on the selection of the Action Type choice column, if F orders are picked by users in the power apps form, the serial number range would be 0001-3499. If seaport is picked, it would be from 3501-3599. This would reset every fiscal year. See attached sample if that makes more sense.

The serial number would reset to 0001-3499 for F orders every fiscal year. And seaport would reset from 3501-3599 every fiscal year. I want all the fiscal year to be on the same list and just have a different list view to separate the fiscal years. The fiscal year column is a choice field with values of years in the future because users should be able to request a serial number for the future fiscal years. I created the list, the power apps forms, but I am having issues with the logic with power automate condition. Here's what my pseudo condition is like:

user fills out form in power apps -> power automate grabs the information -> condition what action type it is and what fiscal year it is. If action type is F orders, assign value 0001 if it doesn't exist. if it does, autogenerate the next number.

I guess my issue is I am not quite sure how to implement the logic in power automate or how to even go about it since I just started messing around with power automate. Thank you in advanced for the assistance!

[Andrew_J]

@lisbonc1987 

I am going to try and help but before I do I need to confirm that you will be using a SharePoint List or Lists for this activity.  As the images above seem to show an excel file.

I am going to go on the premis that you will use a SharePoint List, as I think this is the way to go in my opinion.  I think you need two lists one for the F orders and the other for SeaPort mainly becasue of the numbering issue such as 0001 to 3499.  I cannot see a way of having two diffrent numbering systems in one list unless you use a CASE based on the F Order and SeaPort selection in PowerApps.  I have no experince of PowerApps.  Maybe you need to get one autonumbering system to work say F Order from a manual trigger point of view and then when that works how you want, you add the SeaPort into the mix.  And do some more testing.

The fiscal year is of concern, you have not eluded to when this from and to but how would you handle this change from 2024 to 2025, and 2026 etc.  Can the F Order refrence be 2024 0001 or would that not work for whoever recieves the information, the same would be for SeaPort 2024 3501.  Maybe have a look at the following:

https://powerusers.microsoft.com/t5/Building-Flows/Generate-a-number-when-a-new-form-is-submitted/td...

I am not sure weather I have helped on or not but this is third time I have been to your query and thought the words above might help and have some value.

Regards,

Andrew 

[lisbonc1987]

Andrew,

In regards to your reply below:

"The fiscal year is of concern, you have not eluded to when this from and to but how would you handle this change from 2024 to 2025, and 2026 etc.  Can the F Order refrence be 2024 0001 or would that not work for whoever recieves the information, the same would be for SeaPort 2024 3501."

This is the issue that I am having using power automate. Because every year, the numbers, based on the actrion type, would reset back to, let's say F orders, 0001-3499. Right now, the fiscal year column is a choice column with pre-populated for years 2024, 2025, 2026 etc. So users would pick a fiscal year.

My thought process for power automate is to first look if the fiscal year exists, if it does, look at the action type exists. If it does, increment the number. If it doesn't then start the number with the referenced number range.

I created a lookup list with the action types and reference range of numbers. Gonna see if that works. 

And yes, I am using a list. The sceenshot is just a rough draft from excel so I can just put the information that I need real quick. I currently have this working in atlassian confluence but we are slowly migrating to Microsoft apps.

[Andrew_J]

@lisbonc1987 

I am going to continue with the above as I quite enjoy this type of colaboration. 

I am from the UK, we have a tax year that goes from April to March each year, lets say that this is your Fiscal Year which I know it not likely to be but hear me out.

I think you need to automaticaly change the year based on say the start being 01/04/2024, UK format for date, this could be done by a DATETIME fuction based on UTCNOW.  Likewise if the YYYY changed then so would the F Order or SeaPort number change back to 0001 and 3500 respectivly.  This negates the need for the drop down on the fiscal year but you would still want to output this in your list.  I have seen in examples where people have got a flow working from item no 1 then adjusted this when the flow went live to last number on the old system.  Could you cope with downtime of two days between the old and the new.   

I assume whoever you are putting the system in for and the peson receiving it knows about F Order or SeaPort and would also understand the numbering.  I only suggested 2024 0001 so all parties would be able to better track the number down if required as someone may say 0025 and you then have to ask which year, if you had 2024 0025 you would not have to ask the question.

Happy to keep collaborting on this for you but how the flow would look is something I cannot help with, that will be over to you.

Regards,

Andrew