Flow is not working

[mel_]

Hi, can someone help me with my flow? I wanted to auto create ticket id, where there are several type of request (which is in choice form in sharepoint list), here is my current formula in automate, i am also not sure this is correct or not,

concat(if('Type of Request'="Data Quality Issue","DQ ",if('Type of Request'="New Data Field","DF ",if('Type of Request'="Data Field Update","DF_Up ",if('Type of Request'="New BI Dashboard","BI ",if('Type of Request'="BI Dashboard Update","BI_Up ",if('Type of Request'="New Data Mart","DM ",if('Type of Request'="Data Mart Update","DM_Up ",if('Type of Request'="New Data Extraction Report Template","DE ",if('Type of Request'="Data Extraction Report Template Update","DE_Up ",""))))))))), formatDateTime(utcNow(),'yyyyMMdd'))

the end result should be like this, example DQ 20240123 | 1

where 1 is the number of ticket for the particular type of request

it's a little complicated since the number need to follow each type of request

Thanks in advance!

[v-jefferni]

Hi @mel_ ,

Could you please share more details? Where do you want to use the expression? There should be many dynamic contents you need to select from previous actions' outputs and use in this expression.

Best regards,

[mel_]

So basically I have 9 different type of request(Data Quality Issue, New Data Field, Data Field Update, New BI Dashboard, BI Dashboard Update, New Data Mart, Data Mart Update, New Data Extraction Report Template, Data Extraction Report Template Update), which is a dropdown selection in power apps. I want to generate a unique ticket ID for each of type of request, for example for Data Quality Issue, the ID will be like this, DQ yyyymmdd | x, where x is number of ticket for Data Quality Issue. Same goes to each type of request, for example for New Data Field, the ID will be like this, DF yyyymmdd | y, where y is number of ticket for New Data Field. How can i do this in Automate? The tickets that have been submitted in power apps also need to be updated in sharepoint list, means the ticket ID in power apps need to be in sync in the list

[Ethan_R]

Hi @mel_ ,

I'll suggest an advise:

Step 1: Create a Compose Block for storing your Master data like this format

{
    "Data Quality Issue": "DQ",
    "New Data Field": "DF",
    "Data Field Update": "DF_Up",
    "New BI Dashboard": "BI",
    "BI Dashboard Update": "BI_Up",
    "New Data Mart": "DM",
    "Data Mart Update": "DM_Up",
    "New Data Extraction Report Template": "DE",
    "Data Extraction Report Template Update": "DE_Up"
}

Step 2: Now, fetch all records/items from your SP with a query (do check the query, I haven't worked on SP a lot) but it will mostly be 

<Column Name in SP> eq <Type of Request that triggered flow>

Example
'Type of Request' eq <dynamic expression>(triggerBody()?['Type of Request'])

Step 3: Use a Compose Block to get your new ticket number, below is the formula

Concat(outputs('Compose')?[triggerBody()?['Type of Request']], ' | ' ,add(length(<SP List>), 1))

Do match the names as per your Flow action names and SP columns and info

This will generate new Ticket number as per your request

Hope this helps

[mel_]

Thanks for the reply, but may i ask, does the above steps start with PowerApps v2 trigger? Sorry im kinda confused on how to start with this trigger

[Ethan_R]

Hi @mel_ ,

How are you planning to trigger the flow?

1. Is PowerApps creating record in SP directly or

2. You are sending request to Power Automate to create new record in SP?

[mel_]

im imagining it to be like this, the sharepoint list act as the database and power apps as the platform, so when user go to power apps and choose what type of request of ticket they want to raise, they will be navigated to the below screen, and the ID will automatically generated. When the user click submit, all of the record including the ticket ID will be updated or created in the SP list. The Type of Request column in SP list is in choice type format.

[Ethan_R]

Hi @mel_ ,

That's good, then if you are connecting and creating a Request Ticket from PowerApps which in-fact has direct connection to SP, then why you are thinking of introducing Power Automate flow? 

In case you need to make modifications on the Record that is getting submitted in PowerApps, you can set other information for fields that are hidden.

Or set dynamic default values based on certain field information that is filled by User.

Let me know if this helps

[mel_]

Im thinking of using Automate since the ID itself needs to be unique, like what i have described above.

The number of tickets need to follow each type of request, not general number of ticket for the whole request.

Lets say users have requested these tickets,

first ticket is Data Quality Issue and second ticket is BI Dashboard, the ID for each of them will be DQ yyyymmdd | 1 and BI yyyymmdd | 1

[Ethan_R]

Hi @mel_ ,

The same logic can be done on PowerApps as well. Although for large data, power Automate is fine as well.

Now, there are 2 ways to execute this

Method 1: Create record from PowerApps to SP and then use SP trigger for item/record created. You will get entire details and later calculate the ID and update the record/list.

Method 2: Send user's information from PowerApps to Automate using 'PowerApps V2' trigger and then create record with ID from flow to SP.

As you mentioned about PowerApps V2 trigger, Add input parameters in Trigger, (all parameters in sequence) and then add the Flow in PowerApps.

When you call the Flow by <FlowName>.run( param1, param2, param3,.... ) this would be the syntax.

[Note: When you use 'Respond to flow or App' action, you can send response back to PowerApps. Therefore, you can store the response in PowerApps as variable or collection]

Logic: I have described the logic before, it will be exact same for both methods.

(Also, do tag with @ symbol so I can get notified if there's any update on this thread)

Hope this helps