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 .
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.
Where are you using your formula? As an Initial value?
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.
I will change it and see how it works out.
@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!
More details on Steveโs warning: help.appsheet.com - Sequential Keys Sequential Keys help.appsheet.com
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |