Answered

Auto-increment column by one depending on other column

(1) Share

Report

Posted on by kzoltan

Hi All,

For simplicity, I have two columns: system and counter. I would like to automatically increment the counter column by one, but depending on what system is selected, so for system A, system B and system C I would use 1...2...3...4...5 etc. I assume that I should use some kind of maximum function. In the example below, for a new record A, the counter is 4, for record B it is 3. There are several people working in the application, so the value of the counter should be calculated directly before patching.

I would appreaciate any help.

Thanks,

Zoltan

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

KeithAtherton 3,705 Most Valuable Professional on at

Like (1)

Report
Hi @kzoltan,

Try the following code, where the counter is incremented when new records are added to the data collection:

ClearCollect(colSystemCounter, Blank()); Set(gblSystem, "A"); Collect(colSystemCounter, { System:gblSystem, Counter:Max(Filter(colSystemCounter, System = gblSystem), Counter) + 1 } ); Set(gblSystem, "B"); Collect(colSystemCounter, { System:gblSystem, Counter:Max(Filter(colSystemCounter, System = gblSystem), Counter) + 1 } ); Set(gblSystem, "C"); Collect(colSystemCounter, { System:gblSystem, Counter:Max(Filter(colSystemCounter, System = gblSystem), Counter) + 1 } ); Set(gblSystem, "A"); Collect(colSystemCounter, { System:gblSystem, Counter:Max(Filter(colSystemCounter, System = gblSystem), Counter) + 1 } ); Set(gblSystem, "B"); Collect(colSystemCounter, { System:gblSystem, Counter:Max(Filter(colSystemCounter, System = gblSystem), Counter) + 1 } ); Set(gblSystem, "A"); Collect(colSystemCounter, { System:gblSystem, Counter:Max(Filter(colSystemCounter, System = gblSystem), Counter) + 1 } ); ClearCollect(colSystemCounterSorted, SortByColumns(colSystemCounter, "System", SortOrder.Ascending, "Counter", SortOrder.Ascending ) );

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Follow me online.

Was this reply helpful? Yes No
kzoltan 27 on at

Like (1)

Report

Hi @KeithAtherton ,

Many thanks for the answer.
Your solution is not exactly what I was looking for, but it helped me a lot.

1.) Created a collection:

ClearCollect(
colSystemCounter,
ShowColumns(
SP_list_name,
"System",
"Counter"
)
);

2.) Created an "Edit form" control and in the update property of Card: Counter, I put the following code:

Max(Filter(SP_list_name,System = SystemCardValue.Text), Counter + 1)

However there is a delegation warning:
"Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The 'Max' operation is not supported by the connector"

Is there any way to get rid of this warning? There will be 5-10k records in the datasets.

Thanks,
Zoltan

Was this reply helpful? Yes No
Verified answer

KeithAtherton 3,705 Most Valuable Professional on at

Like (1)

Report
Try changing the code to the following so that the filtered list is stored as a local data collection first, then you can apply the Max function. If you get more delegation warnings, you may need to trial-and-error the code:

ClearCollect(colMyList, Filter(SP_list_name,System = SystemCardValue.Text)); Set(gblCounterValue, Max(colMyList, Counter + 1));

Also, here's a useful article regarding SharePoint Delegation for Power Apps:

https://www.matthewdevaney.com/sharepoint-delegation-cheat-sheet-for-power-apps/

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Follow me online.

Was this reply helpful? Yes No
kzoltan 27 on at

Like (1)

Report

Thanks, it is working, perfect!

Was this reply helpful? Yes No
KeithAtherton 3,705 Most Valuable Professional on at

Like (1)

Report

Good news! My pleasure. Thanks for the update.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Follow me online.

Was this reply helpful? Yes No