cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thunter
Post Prodigy
Post Prodigy

Calling ID from SQL table not updating

I have an application that I built where we record company incidents. On the screen where a user enters a new incident, it assigns an incident number by using the Type of Incident, the Year, and then the ID plus 1 from the SQL table. It was working fine up until a week ago. It is not updating the ID, not sure what to check. I have refreshed the connections to SQL, retyped formula, etc. Any help would be appreciated. Thank you.

 

TypeValue.SelectedText.Value & "-" & RptYearValue.Text & "-" & Last('[dbo].[wynne_incidents]').ID +1
1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @thunter ,

 

Could you tell me:

  1. How many records are in the data source?
  2. Whats not updating mean? (Did you create a new record and the ID value of the record no longer automatically increases by 1?)

 

I assume:

You create a new record and the ID value of the record no longer automatically increases by 1.

 

If my assumption is correct, let me explain why you encountered this problem.

The point is the delegation. Last() function does not support delegation.

If your delegation settings are not modified, it will process 500 pieces of data by default.

You could refer to the following document:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

https://docs.microsoft.com/en-us/connectors/sql/

 

So, you could use Max() function that supports delegation instead of Last() function.

You could try the following code:

TypeValue.SelectedText.Value & "-" & RptYearValue.Text & "-" & Max('[dbo].[wynne_incidents]',ID) +1

 

Best Regards,

Wearsky

View solution in original post

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

Hi @thunter ,

 

Could you tell me:

  1. How many records are in the data source?
  2. Whats not updating mean? (Did you create a new record and the ID value of the record no longer automatically increases by 1?)

 

I assume:

You create a new record and the ID value of the record no longer automatically increases by 1.

 

If my assumption is correct, let me explain why you encountered this problem.

The point is the delegation. Last() function does not support delegation.

If your delegation settings are not modified, it will process 500 pieces of data by default.

You could refer to the following document:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

https://docs.microsoft.com/en-us/connectors/sql/

 

So, you could use Max() function that supports delegation instead of Last() function.

You could try the following code:

TypeValue.SelectedText.Value & "-" & RptYearValue.Text & "-" & Max('[dbo].[wynne_incidents]',ID) +1

 

Best Regards,

Wearsky

View solution in original post

@v-xiaochen-msft 

 

I tried using MAX but I get an error, do I need to make other changes? Thank you for your help!

 

power1.png

@v-xiaochen-msft 

 

Sorry I had a typo in the  formula. It worked for creating the correct Incident Number by adding 1 to the ID.

 

I now have a drop down box that doesn't show all the incident numbers. Does this have to do with delegation also?

 

Here is what I have in the ITEMS of the dropdown:

'[dbo].[wynne_incidents]'.incident_num

@v-xiaochen-msft 

 

I solved my problem by creating a radio button search which activates a drop down depending on the type of incident. This helps with the number of records being pulled by the drop down and no longer have an issue with delegation or "too many" records.

 

thank you for your help!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,039)