Calling ID from SQL table not updating

[Anonymous]

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

[v-xiaochen-msft · Accepted solution]

Hi @Anonymous ,

Could you tell me:

1. How many records are in the data source?
2. What’s “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

[Anonymous]

@v-xiaochen-msft 

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

[Anonymous]

@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

[Anonymous]

@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!