Answered

Retrieval of data from SharePoint list with the next highest value

(0) Share

Report

Posted on by rob4681

Hello,

I have a list which I need to step through a numerical value called OpCode based on the last OpCode value. Is there a way to reference the current OpCode value and return the next highest OpCode value from the list, the list isn't in numerical order.

Thank you in advance.

Rob

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @rob4681 ,

What would you like to do with the next OpCode? Could you please share more details about your scenaio?

Basically, the logic is getting all items that has bigger OpCode value than that of current item's, sort them ascending and get the first one. The formula looks something like this:

First(Sort(Filter(SPList, OpCode > ThisItem.OpCode), OpCode)).OpCode

Best regards,

Was this reply helpful? Yes No
rob4681 on at

Like (0)

Report
Copy link

Link copied!

Thanks Jeffer,

Does this sort the list from a specific value, I don't want the highest value just the next highest value item. Could you give an example of the code.

Thanks,

Rob

Was this reply helpful? Yes No
rob4681 on at

Like (0)

Report
Copy link

Link copied!
Still won't compile.

Set(ProdDirOut,DataCardValue5_1.Selected.Value); Set(CurrCount, LookUp(BatchTrack, 'Batch ID'=DataCardValue16_2.Text && 'Wafer ID'=DataCardValue28_2.Text, OpCode)); Set(NextCount, First(Sort(Filter(ProdDirOut, OpCode01 > CurrCount), OpCode01)).OpCode01); Set(NextStep, Switch(ProdDirOut, "Prod1", LookUp(Prod1, OpCode01 = Value(NextCount), Step), "Prod2", LookUp(Prod2, OpCode01 = Value(NextCount), Step), "Trial", LookUp(Trial, OpCode01 = Value(NextCount), Step) ));
OpCode01 is a column name in all the lists Prod1, Prod2 and trial, where OpCode is the name of the list in BatchTrack where the current value is. I am quite new to this so might have missed something obvious.

The part which won't compile is the Set NextCount, First...........

Thanks again.

Rob

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @rob4681 ,

What is this variable:

Set(ProdDirOut,DataCardValue5_1.Selected.Value);

BR,

Was this reply helpful? Yes No
rob4681 on at

Like (0)

Report
Copy link

Link copied!

It will always be one of the options in the switch function. All the values in the switch function are lists.

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @rob4681 ,

That's it. Data sources should be static names when using in formulas. So, just like the variable NextStep, you need Switch function as well in NextCount variable:

Set(ProdDirOut,DataCardValue5_1.Selected.Value); Set(CurrCount, LookUp(BatchTrack, 'Batch ID'=DataCardValue16_2.Text && 'Wafer ID'=DataCardValue28_2.Text, OpCode)); Set(NextCount, First(Sort(Switch(ProdDirOut, "Prod1",Filter(Prod1, OpCode01 > CurrCount), "Prod2",Filter(Prod2, OpCode01 > CurrCount), "Trial",Filter(Trial, OpCode01 > CurrCount) ), OpCode01)).OpCode01 ); Set(NextStep, Switch(ProdDirOut, "Prod1", LookUp(Prod1, OpCode01 = Value(NextCount), Step), "Prod2", LookUp(Prod2, OpCode01 = Value(NextCount), Step), "Trial", LookUp(Trial, OpCode01 = Value(NextCount), Step) ) );

Best regards,

Was this reply helpful? Yes No
rob4681 on at

Like (0)

Report
Copy link

Link copied!

Thanks Jeffer,

I still can't get this to work but will keep trying and accept once I solve it.

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @rob4681 ,

I've made a mistake in the formula, the OpCode01 column should be static as well. So, please try below:

Set(ProdDirOut,DataCardValue5_1.Selected.Value); Set(CurrCount, LookUp(BatchTrack, 'Batch ID'=DataCardValue16_2.Text && 'Wafer ID'=DataCardValue28_2.Text, OpCode)); Set(NextCount, Switch(ProdDirOut, "Prod1", First(Sort(Filter(Prod1, OpCode01 > CurrCount), OpCode01)).OpCode01, "Prod2", First(Sort(Filter(Prod2, OpCode01 > CurrCount), OpCode01)).OpCode01, "Trial", First(Sort(Filter(Trial, OpCode01 > CurrCount), OpCode01)).OpCode01 ) ); Set(NextStep, Switch(ProdDirOut, "Prod1", LookUp(Prod1, OpCode01 = Value(NextCount), Step), "Prod2", LookUp(Prod2, OpCode01 = Value(NextCount), Step), "Trial", LookUp(Trial, OpCode01 = Value(NextCount), Step) ) );

Best regards,

Was this reply helpful? Yes No