Solved: Initial value will not populate - Page 2

tvinci · 05-19-2020 10:26 AM

Hello AppSheet Community

I am attempting to have a field in my form prepopulated, so I wrote this expression, but the field still will not be prepopulated. What did I do wrong?

Thanks.
Tiffany

ifs(index(split(agid test entry[well position],’-’),2)=‘4C’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))+1) , ‘1A’),
index(split(agid test entry[well position],’-’),2)=‘1A’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘1B’),
index(split(agid test entry[well position],’-’),2)=‘1B’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘1C’),
index(split(agid test entry[well position],’-’),2)=‘1C’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘2A’),
index(split(agid test entry[well position],’-’),2)=‘2A’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘2B’),
index(split(agid test entry[well position],’-’),2)=‘2B’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘2C’),
index(split(agid test entry[well position],’-’),2)=‘2C’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘3A’),
index(split(agid test entry[well position],’-’),2)=‘3A’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘3B’),
index(split(agid test entry[well position],’-’),2)=‘3B’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘3C’),
index(split(agid test entry[well position],’-’),2)=‘3C’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘4A’),
index(split(agid test entry[well position],’-’),2)=‘4A’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘4B’),
index(split(agid test entry[well position],’-’),2)=‘4B’,
concatenate((max(extractnumbers(index(split(agid test entry[well position],’-’),1)))) , ‘4C’),
)

tony1

This reminds me of a question about generating sequential invoice numbers that came up the other day. You might want to check out that thread for inspiration: Incrementing Number when its a Key / Invoice Number Generation

Do you have just a fixed set of well numbers? If so, you might want to use a lookup table that has a column for well numbers and another column for the “successor” well number. You could then use LOOKUP() to initialize the value.

Otherwise, it might be easier to have your Well Position split into a column for each piece, so the row with 2-4C would look like:

Number1: 2
Number2: 4
Letter: C
Well Number: 2-4C

I’d also add a reference to the most recently added row. That should also help with writing your formulas. See this app for an example: https://www.appsheet.com/samples/Initialize-forms-with-most-recent-value?appGuidString=18b39921-b8a8...

Then you could write separate initial values for the columns. For example, Number1 should be incremented if the most recent row had 4C:

IF(
  AND([Most Recent Row].[Number2] = 4, [Most Recent Row].[Letter] = C),
  [Most Recent Row].[Number1]+1,
  [Most Recent Row].[Number1]
)

You could write similar formulas for the other parts, and then your Well Position initial value would be [Number1] & "-" & [Number2] & [Letter]. You could hide the columns, so the end-user doesn’t see them.

Other community members might find a more concise way to do this…

BTW, I think the formula you had in your post was using agid test entry[well position] incorrectly. That expression will return the entire list of values for the Well Position column.

View solution in original post