Find the maximum batch number in the database

(0) Share

Report

Posted on by sahachiranjit32

143

Hi All,

In my Sql db Title column group of attachments are stored as batch 1, batch 2 so on.. I want to check through PowerFx max no of batch number stored in database then increment it. if it is Null then It should be batch 1 if it is batch 1 then stored as batch 2 and so on.... below code

// initialize batch counter
ClearCollect(batchCount, {value: 1});

// find the maximum batch number in the database
ClearCollect(
maxBatch, LookUp(myimages, !IsBlank(Title))

);

// check if the maximum batch number exists and increment it
If(
CountRows(maxBatch) > 0,
Patch(
batchCount,
First(batchCount),
{value: Value(Right(First(maxBatch).Title, 1)) + 1}
),
// otherwise increment the batch counter
Patch(batchCount, First(batchCount), {value: First(batchCount).value + 1})
);

In code : // find the maximum batch number in the database
ClearCollect(
maxBatch, LookUp(myimages, !IsBlank(Title))------ I have troubled find the max batch number in db . Also lookup function might not work in large dataset..... can anyone help it

Thanks,

Chiranjit

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

rswain 173 on at

Like (0)

Report
To find the maximum batch number in the database, you can use the Max function to get the maximum value of the Title column where the Title column contains the string "batch" and a number. Here's an example: ClearCollect(maxBatch, Filter(myimages, StartsWith(Title, "batch") && IsNumeric(Substitute(Right(Title, Len(Title)-5), ".", "")))); ClearCollect(maxBatchNumber, {value: If(CountRows(maxBatch) > 0, Max(Table(maxBatch, "value")), 0)}); This will first filter the myimages table to only include rows where the Title column starts with the string "batch" and is followed by a number. Then, it will calculate the maximum value of the value column in the maxBatch collection. If there are no rows in the maxBatch collection, it will set the maximum batch number to 0. Note that this assumes that the batch number is always at the end of the Title column and is separated by a space from the preceding text. If this is not the case, you may need to modify the Substitute and Right functions to extract the correct batch number.

Was this reply helpful? Yes No
sahachiranjit32 143 on at

Like (0)

Report

There is an issue with this line:

&& IsNumeric(Substitute(Right(Title, Len(Title)-5), ".", ""))) --- Delegation warning

ClearCollect(maxBatchNumber, {value: If(CountRows(maxBatch) > 0, Max(Table(maxBatch, "value")), 0)}); -- can't use non record value .

Was this reply helpful? Yes No