Determine if a new record has been added to a read-only table

[kman42]

Can someone suggest a strategy for determining if new records have been added to a read-only table? I use our data warehouse tables as the datasource for the app, but the tables are read only. Is there a way to store the last record id between app sessions and check to see if something has been added? Alternatively, I have access to a read/write sql database and I could create a table with records for each user and the last record id from each data pull, but I'm not sure if that is the best strategy.

Thanks!

[yashag2255]

Hi @kman42 

If you have a column in your table, which determines the last updated time or an auto increment number, then you can use the filter query to get the specific number.

Expression: 

Set(LastEntry,First(Sort(SQLDataSource,AutoincrementNo,Descending)).AutoincrementNo)

Please replace SQLDataSource with your table and AutoincrementNo with the specific attribute.

Also, if you are looking for something specific to your case, please share the schema of your source along with the operation you are trying to do, then we will be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[kman42]

Thanks, that helps with half of the solution, but what is the best strategy for storing the last increment of the auto increment id between sessions for various users?

[yashag2255]

HI @kman42 

For doing so, you can add a timer control with below properties that refreshes the datasource after a certain duration and reset the variable, you can set this duration by yourself while configuring the contro. There is no max limit.

Timer Control:

AutoStart: true

Repeat: true

AutoPause:false

OnTimerEnd: Refresh(SQLDataSource); Set(LastEntry,First(Sort(SQLDataSource,AutoincrementNo,Descending)).AutoincrementNo)

Duration: 300000 (this needs to be passed in milliseconds)

Visible: false (If you want to hide the control from screen)

With above settings, the datasource will refresh every 5 minutes, you can reduce this value.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[kman42]

Ok, but I'm still not clear on the best strategy to store the variable between app sessions. For example, I log on and pull the data from the data warehouse. I get the last increment of the id. Where do I store that so that the next time I open the app I can compare it to the last id from the new data warehouse query?

Is there a way to store a local variable between app sessions or do I have to store it for each user in a sql table?

[PaulD1 · Accepted solution]

If your App runs in a Player (not in a browser), e.g. through the Android, iOS or Windows Store Power Apps player, you can use the SaveData command to store values directly on the device that are persisted between sessions and LoadData to retrieve the value, e.g. in the App OnStart event.

If your App runs in a browser then SaveData and LoadData are not currently supported and you'll need to Patch to a datasource such as a SQL table or SharePoint list and store the user - User().Email - along with the name of the table and its highest ID value.

[kman42]

Brilliant! Thank you.

[yashag2255]

Hi @kman42 Were you able to resolve the issue?

If my reply has answered your question or solved your issue, please mark the question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!