Initial value will not populate

Hello AppSheet Community :slight_smile:

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’),
)

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?

1 Like

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.

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-46f4-b419-2acbf067ba08

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.

3 Likes

I did this and it fixed it! Thank you :slight_smile:

2 Likes