Suggested values in text type column with different conditions

[hhulas23]

Hey guys,

i need your help. I tried a lot, but I couldn’t figure it out.

the problem i am trying to solve:
I want to get an auto computed list of suggested values for the column called ‘Nr.’.
as you can see, the column ‘Nr.’ is not a number type, because sometimes I need to add an a, b, c or d at the end of a value in this column, that is why i chose text type for it.

so for example:
if you have a look at the preview listing on the Screenshot I attached, you will see the last rows I added are at the top rows (sorted descanding). if I add a new row and fill in the value for ‘Nr.’ in the form, I want the auto compute to list me 3 (sometimes 2) suggestions:

1. suggestion value: 51

• because the highest value so far was 50a and 50b

2. suggestion value: 51a

• it might be, that I need 51a instead of 51, because there is one row going to follow that belongs to this one, so 51b
  (if an ‘…a’ is used, a row with ‘…b’ with follow, in every case. otherwise I would not use ‘a’)

3. suggestion value: 50c

• because the last row is 50b, it is possible, that this new row is the continuation of 50b.
• if the last row does not contain an a, b, c or d than this 3. suggestion is not necessary.

4. allow other values:

• a possibility to type in a random value manually

I wasted a lot of time with trying to find a solution by combining various formulas, reading many of your documentations and community contributions. I hope someone here can help me. ff something is not clear enough, please let me know.

Maybe this information will help you any way…
Unfortunately i have deleted all formula attempts I made already, but they looked something like that, as far as i can remember (obviously i now know that e.g. MAXROW is not possible because the column type is text, but maybe it helps you to understand my purpose better):

LIST(
(MAXROW( “Bestellungen” , ”Nr.” )),
(CONCATENATE( {1. suggestion} ) , ”a” )),
(IFS( ({if last row column ‘Nr.’ contains an ‘a’}) , ({than take this value an substitute the ‘a’ with a ‘b’}) , ({if last row column ‘Nr.’ contains an ‘b’}) , ({than take this value an substitute the ‘b’ with a ‘c’}) , ({if last row column ‘Nr.’ contains an ‘c’}) , ({than take this value an substitute the ‘c’ with a ‘d’}) , ({if last row column ‘Nr.’ contains no letters}) , (than BLANK ) ))
)

thanks!

[Steve]

I’m going to say there’s no good way to do what you want.

[hhulas23]

What would you say, if i change the column typ?
instead of e.g. 50a i would i could use 50.1
or instead of 50b i use 50.2
so replace the letters with a dot and digits.

Or could you think of another way to get in done if i do certain changes to the value or column structure?

[Steve]

hhulas23:

What would you say, if i change the column typ?
instead of e.g. 50a i would i could use 50.1
or instead of 50b i use 50.2
so replace the letters with a dot and digits.

This would definitely be easier to handle, but your apparent interest in allowing the user to enter arbitrary values (that presumably don’t confirm to your convention) greatly complicates everything.

What is the reason for this numbering scheme? Knowing your goal may help us provide better suggestions.

[hhulas23]

This app is a project for highschool. for example if theire is an incoming order for a product, i add a row with data like customer name, order date, product amount, product details and so on.
if a customer orders two products, than i add a second row with the same values except from product, productprice and cargo price.
the ‘Nr.’ at the beginning should make clear, that these two rows are related to each other. basically ‘Nr.’ just shows, that these two rows belong to one order.

or if a few weeks after the order, the customer decides to send the product back, than again another row will be added, with the same ‘Nr.’ as the primal order but the ‘a,b or c’ added, so that also here the relation to another row is clear.

[Steve]

hhulas23:

This app is a project for highschool.

I can’t say I’m comfortable providing any further assistance.

[hhulas23]

alright, thanks anyway

[Lynn]

Hi @hhulas23 Have you had a look at the “Order Capture” sample app. It may be useful.

[hhulas23]

no not yet, i will. thanks

[Lynn]

Is this your key column you are dealing with?

[hhulas23]

yes, the column called ‘Nr.’ is my key column