Need a little help with a formula. I have th...

[Ed_Alwine]

Need a little help with a formula.

I have this:

LOOKUP(MAX(HCS Leads[HCS Number]), HCS Leads, HCS Number, HCS Number)+1 (One randomly chosen value from this list ( …The list of values of column ‘HCS Number’ …from rows of table ‘HCS Leads’ …where this condition is true: ((The value of column ‘HCS Number’) is equal to (MAX( …HCS LEADS[HCS Number])))) + 1 Its supposed to look up the max number in a column and then write the next highest number to the record.

But it’s not quite doing it. Maybe because the numbers are out of sequence in the column? Maybe because I’m missing something.

Can anyone help me with this expression?

Again it supposed to look up the highest number in the column. Then assign the next number to the record its writing in that column .

[Steven_Coile]

At the very least, you’ve over-complicated things by using lookup(). You can replace your expression with just this:

MAX(HCS Leads[HCS Number])+1

The above says, “find the maximum value in the [HCS Number] column of the HCS Leads table, then add 1 to it.”

Your original formula said, “find the maximum value in the [HCS Number] column of the HCS Leads table, then find the row with that value in the [HCS Number] column of the HCS Leads table and return the value in the [HCS Number] column, and add 1 to it.” Essentially, you found the max number, then used it to find it again,asked for it back, and added 1 to it.

[Steven_Coile]

Where are you using your formula? As an Initial value?

[Ed_Alwine]

LOL well crud. talk about trying to move the cow with a rope tied at both ends. LOL. Thank you.

Its a running customer number the company uses for every sale to keep each record unique even though the same individual might hire use for multiple jobs over time.

So every time a record gets sold it gets the next hcs number in the line.

[Ed_Alwine]

I will change it and see how it works out.

[Steven_Coile]

@Ed_Alwine be careful with your approach, using the HCS Number as a unique identifier. If you have more than one user of your app, each user will only see changes others have made after everyone has synced. Its possible 2 users could start with the same data (say, at the beginning of the day), one user allocates an HCS Number, but another user doesn’t sync to get the first’s changes before also allocating an HCS Number. Because this second user still has old data, they’ll allocate the same HCS Number the first year did! When the second user finally syncs, their changes will overwrite the first user’s!

[pravse]

More details on Steve’s warning: help.appsheet.com - Sequential Keys Sequential Keys help.appsheet.com