cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Problem selecting records from SQL DB

Hi everyone.

I present my problem to you:
I created an application for configuring an order. When the order is confirmed, the Patch function writes the data in the SQL DB and subsequently calls a stream which, based on the QuotationID that I pass to it, creates an adaptive card on Teams with the data of the record just entered. This works fine until I place an order at a time. During the tests, if I make several entries (with different devices, tablets, PCs, smartphones) close together, the flow always draws the same record (the last one) because the db is not updated in time. I think it can be linked to the asynchronous management of tasks. As a possible solution I thought of a possible SQL transaction.
is it possible to run an SQL transaction in PowerApps?

flow.PNG

 

This is what I got doing two orders in a row. I got twice the same QuotationID (466 instead of 466 and 467) .
Please, Can anyone tell me a better and feasible solution?

 

Thanks

Andi

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi Andi,

Rather than Patching into '[dbo].[Quotation]' and calling Last('[dbo].[Quotation]') to retrieve the ID, you can retrieve the newly created QuotationID through the return value from Patch.

Here's how would amend your existing formula to use this technique:

 

Set(
NewQuotationRecord;
Patch( '[dbo].[Quotation]'; Defaults('[dbo].[Quotation]'); { RevisionNr: 1; CustomerID: Value(CustID); ProductID: [@counterDef]; Price: ((LookUp('[dbo].[Product]';ProductID=[@counterDef];Price)*Value(txtInpNPezzi.Text))*(100-LookUp('[dbo].[Customer]';CustomerID=Value(CustID);Discount))/100); QuotationData: Today(); QuotationQTY: Value(txtInpNPezzi.Text); QuotationStateID: 2; Discount: LookUp( '[dbo].[Customer]'; CustomerID = Value(CustID); Discount ); FlowRate: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; FlowRate ); Weight: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Width ); EnergyEfficiency: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; EnergyEfficiency ); NoiseEmission: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; NoiseEmission ); MotorVibration: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; MotorVibration ); Width: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Width ); Depth: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Depth ); Height: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Height ); Sensor: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Sensor ); Thermostat: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Thermostat ) } )
);;

Refresh('[dbo].[Quotation]');;
'LEF-OrderOutput-Upgrade'.Run(Text(NewQuotationRecord.QuotationID);"Confirmed";"")
;;Refresh('[dbo].[vw_HistoryQuotation]');; Navigate( ConfirmedScreen7; ScreenTransition.Fade )

Here, we store the newly created record in a variable called NewQuotationRecord, and we can then use that to retrieve the new QuotationID. Hopefully, this will resolve your problem.

View solution in original post

Hi @AndiRodi ,

I agree with @timl 's thought almost. Sometimes due to formula execution issue, the Last() function may not get the latest inserted record within your SQL Table.

 

On your side, please consider set the result the Patch formula returns into a global variable. If the Patch formula executes successfully, the QuotationID value would be included within the returned result.

 

Please consider modify your formula as below:

Set(
      SubmittedRecord;     /* <-- Store the result the Patch function executes into a global variable */
      Patch(
            '[dbo].[Quotation]';
             Defaults('[dbo].[Quotation]');
            {
             RevisionNr: 1;
             CustomerID: Value(CustID);
             ProductID: [@counterDef];
             Price: ((LookUp('[dbo].[Product]';ProductID=[@counterDef];Price)*Value(txtInpNPezzi.Text))*(100-LookUp('[dbo].[Customer]';CustomerID=Value(CustID);Discount))/100);
             QuotationData: Today();
             QuotationQTY: Value(txtInpNPezzi.Text);
             QuotationStateID: 2;
             ...
... Height: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Height ); Sensor: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Sensor ); Thermostat: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Thermostat ) } ) );;
Refresh('[dbo].[Quotation]');;
'LEF-OrderOutput-Upgrade'.Run(Text(SubmittedRecord.QuotationID);"Confirmed";"");; /* <-- Modify formula here */
Refresh('[dbo].[vw_HistoryQuotation]');;
Navigate(ConfirmedScreen7; ScreenTransition.Fade)

Please take a try with above solution, then try your app again, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Super User III
Super User III

Hi Andi,

How are you generating your QuotationIDs? If you're generating them from within PowerApps by finding the maximum QuotationID and adding 1, you can maybe minimise the occurrence of duplicate IDs by refreshing the data source before finding the next QuotationID.

If it were me, I would create a SQL Stored Procedure to find the next QuotationID and to write whatever data you need into the SQL DB (rather than call Patch from PowerApps). You would then call this Stored Procedure from within your Flow. Within a Stored Procedure, you can transaction bind any SQL operations that you want to carry out.

Hopefully, that's of some help to you.

hi @timl 

 

The QuotationID is created as autoincremental number. The only thing that i do is to pass it as the parameters for the run() method of flow. 

You said "If it were me, I would create a SQL Stored Procedure to find the next QuotationID and to write whatever data you need into the SQL DB (rather than call Patch from PowerApps). You would then call this Stored Procedure from within your Flow. Within a Stored Procedure, you can transaction bind any SQL operations that you want to carry out."

How can i proceed to do that? can you explan me the steps please?

could be this link useful?

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-... 

thanks a lot for your feedback.

 

Andi

Hi @AndiRodi 

Just to confirm how your workflow works, how exactly do you retrieve the QuotationID that you pass to Flow? Is QuotationID an auto-generated value that SQL Server generates after you insert a record into a table via your call to Patch?

Hi @timl ,

 

exactly, it is an autogenerated value.

 

here the patch code of the confirm button:

Patch(
'[dbo].[Quotation]';
Defaults('[dbo].[Quotation]');
{
RevisionNr: 1;
CustomerID: Value(CustID);
ProductID: [@counterDef];
Price: ((LookUp('[dbo].[Product]';ProductID=[@counterDef];Price)*Value(txtInpNPezzi.Text))*(100-LookUp('[dbo].[Customer]';CustomerID=Value(CustID);Discount))/100);
QuotationData: Today();
QuotationQTY: Value(txtInpNPezzi.Text);
QuotationStateID: 2;
Discount: LookUp(
'[dbo].[Customer]';
CustomerID = Value(CustID);
Discount
);
FlowRate: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
FlowRate
);
Weight: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
Width
);
EnergyEfficiency: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
EnergyEfficiency
);
NoiseEmission: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
NoiseEmission
);
MotorVibration: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
MotorVibration
);
Width: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
Width
);
Depth: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
Depth
);
Height: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
Height
);
Sensor: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
Sensor
);
Thermostat: LookUp(
'[dbo].[Product]';
ProductID = [@counterDef];
Thermostat
)
}
);;Refresh('[dbo].[Quotation]');;'LEF-OrderOutput-Upgrade'.Run(Text(Last('[dbo].[Quotation]').QuotationID);"Confirmed";"");;Refresh('[dbo].[vw_HistoryQuotation]');;
Navigate(
ConfirmedScreen7;
ScreenTransition.Fade
)

 

Thx

Andi

Hi Andi,

Rather than Patching into '[dbo].[Quotation]' and calling Last('[dbo].[Quotation]') to retrieve the ID, you can retrieve the newly created QuotationID through the return value from Patch.

Here's how would amend your existing formula to use this technique:

 

Set(
NewQuotationRecord;
Patch( '[dbo].[Quotation]'; Defaults('[dbo].[Quotation]'); { RevisionNr: 1; CustomerID: Value(CustID); ProductID: [@counterDef]; Price: ((LookUp('[dbo].[Product]';ProductID=[@counterDef];Price)*Value(txtInpNPezzi.Text))*(100-LookUp('[dbo].[Customer]';CustomerID=Value(CustID);Discount))/100); QuotationData: Today(); QuotationQTY: Value(txtInpNPezzi.Text); QuotationStateID: 2; Discount: LookUp( '[dbo].[Customer]'; CustomerID = Value(CustID); Discount ); FlowRate: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; FlowRate ); Weight: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Width ); EnergyEfficiency: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; EnergyEfficiency ); NoiseEmission: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; NoiseEmission ); MotorVibration: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; MotorVibration ); Width: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Width ); Depth: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Depth ); Height: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Height ); Sensor: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Sensor ); Thermostat: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Thermostat ) } )
);;

Refresh('[dbo].[Quotation]');;
'LEF-OrderOutput-Upgrade'.Run(Text(NewQuotationRecord.QuotationID);"Confirmed";"")
;;Refresh('[dbo].[vw_HistoryQuotation]');; Navigate( ConfirmedScreen7; ScreenTransition.Fade )

Here, we store the newly created record in a variable called NewQuotationRecord, and we can then use that to retrieve the new QuotationID. Hopefully, this will resolve your problem.

View solution in original post

Hi @AndiRodi ,

I agree with @timl 's thought almost. Sometimes due to formula execution issue, the Last() function may not get the latest inserted record within your SQL Table.

 

On your side, please consider set the result the Patch formula returns into a global variable. If the Patch formula executes successfully, the QuotationID value would be included within the returned result.

 

Please consider modify your formula as below:

Set(
      SubmittedRecord;     /* <-- Store the result the Patch function executes into a global variable */
      Patch(
            '[dbo].[Quotation]';
             Defaults('[dbo].[Quotation]');
            {
             RevisionNr: 1;
             CustomerID: Value(CustID);
             ProductID: [@counterDef];
             Price: ((LookUp('[dbo].[Product]';ProductID=[@counterDef];Price)*Value(txtInpNPezzi.Text))*(100-LookUp('[dbo].[Customer]';CustomerID=Value(CustID);Discount))/100);
             QuotationData: Today();
             QuotationQTY: Value(txtInpNPezzi.Text);
             QuotationStateID: 2;
             ...
... Height: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Height ); Sensor: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Sensor ); Thermostat: LookUp( '[dbo].[Product]'; ProductID = [@counterDef]; Thermostat ) } ) );;
Refresh('[dbo].[Quotation]');;
'LEF-OrderOutput-Upgrade'.Run(Text(SubmittedRecord.QuotationID);"Confirmed";"");; /* <-- Modify formula here */
Refresh('[dbo].[vw_HistoryQuotation]');;
Navigate(ConfirmedScreen7; ScreenTransition.Fade)

Please take a try with above solution, then try your app again, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @timl  and @v-xida-msft ,

 

Thank you both for the solution. 
The App gives the right output now. 

 

Every think looks good. 

 

Thanks

 

Andi

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (9,800)