Solved: Re: Initial value will not populate

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

tony1

Hi @tvinci. I think there are two routes we could go down (1) try to debug your expression (which is pretty complex), or (2) try to think of a different way to solve your problem that doesn’t involve such a complex formula.

I think it’s worthwhile to try (2) before (1). Could you say a little bit more about what you’re trying to do?

tvinci

Hello Tony,

Thanks for your response. So, as you may or may not be able to guess from the expression, I have a list of “Well Position” values that look like this

1-1A
1-1B
1-1C

and so on. So I wanted to write an expression so that the initial value of “Well Position” increments automatically while following the pattern, so that the user doesn’t have to think about which Well Position to enter into the form.

tony1