web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Problem selecting reco...
Power Apps
Answered

Problem selecting records from SQL DB

(0) ShareShare
ReportReport
Posted on by AndiRodi 133

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

Categories:
Building Power Apps
I have the same question (0)
  • timl Profile Picture
    timl 37,283 Super User 2026 Season 2 on at

    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.

  • AndiRodi Profile Picture
    AndiRodi 133 on at

    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-split-method/ 

    thanks a lot for your feedback.

     

    Andi

  • timl Profile Picture
    timl 37,283 Super User 2026 Season 2 on at

    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?

  • AndiRodi Profile Picture
    AndiRodi 133 on at

    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

  • Verified answer
    timl Profile Picture
    timl 37,283 Super User 2026 Season 2 on at

    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.

  • Verified answer
    v-xida-msft Profile Picture
    v-xida-msft Microsoft Employee on at

    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,

  • AndiRodi Profile Picture
    AndiRodi 133 on at

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 392 Most Valuable Professional

#2
11manish Profile Picture

11manish 136 Super User 2026 Season 2

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 109 Super User 2026 Season 2

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans