appsheet formula

[VASSILY]

I need a formula that generates an alphanumeric code for each shipment a vehicle makes.

eg

Shipping vehicle 1- its code would be A0001

Shipping vehicle 2 - its code would be B0001

New shipment vehicle 1 - A0002

New shipment vehicle 1 - A0003

New shipment vehicle 2 - B0002

[diogolupcosta]

CONCATENATE("A",MAX(LIST([_THISROW].[COLUMN]))+1)

[Joseph_Seddik]

Welcome to the community!

Please read this:  Serial Numbers, If You Must - Google Cloud Community  

[VASSILY]

Thanks Joseph. The link is very instructive but it does not solve my problem. I can see it is "dangerous" the use of serial numbers but... in our case they are cumpulsory.

I will keep on studing about programming which is not particulary easy for those of us who studied greak and latin in high school.

[Steve]

The article @Joseph_Seddik posted shows how to generate serial numbers.

[VASSILY]

Hello Steve and thanks for all your articles. Big fan.

In my particular case the problem is not only generate one column able to create serial numbers. The problem is that every truck in our company must have its own serial number. So, depending on the truck we choose in the trucks column, the "serial number column" must assing the correct serial number according to the truck. It is driving me crazy

[VASSILY]

For Example I used this formula where "C" is the truck labeled C:

CONCATENATE(“C”,RIGHT((“0000” & ([_RowNumber] -1 )), 4))

The problem is alwais the same. I do not know how to create a formula which can distinguish between our different trucks.

[Joseph_Seddik]

What is the problem with your formula?

[VASSILY]

Hello again Joseph.

The formula is right itself. But I always get the same result: C0001

That is O.K. just for the truck labeled "C", but we have 3 trucks. So I need that when in the trucks column I choose a different truck I get a different serial alfanumeric code. Each of the codes with its own serial number:

A1235

C5569

B0256

A1236

C5570

A1237

[Steve · Accepted solution]

(
  [prefix]
  & RIGHT(
    (
      "0000"
      & (
        NUMBER(
          RIGHT(
            ANY(
              SORT(
                SELECT(
                  table[column],
                  STARTSWITH([column], [_THISROW].[prefix])
                ),
                TRUE
              )
            ),
            4
          )
        )
        + 1
      )
    ),
    4
  )
)

[VASSILY]

Hi Steve!

Forgive me if it took me so long to answer to your solution. Too much to do everyday.

Well, I have been working in your formula but I can´t make it work.

For sure it is allright but I just can´t make it happens

[VASSILY]

This is the formula as I adapted it and the result is in the image attached

(

  [PREFIX]

  & RIGHT(

    (

      "0000"

      & (

        NUMBER(

          RIGHT(

            ANY(

              SORT(

                SELECT(

                  DOC.COMERCIAL[Nº DOC. COMERCIAL],

                  STARTSWITH([Nº DOC. COMERCIAL], [_THISROW].[PREFIX])

                ),

                TRUE

              )

            ),

            4

          )

        )

        + 1

      )

    ),

    4

  )

)

[mateo]

Hi @VASSILY 

Using serial numbering is quite dangerous because this is how AppSheet is built but in one of my projects where also needed to use sequential numbers I created automation BOT which was triggered only when new row was being added. It is checking and looking for the highest number in previously existing rows in the table and then adding +1 to this value. App is runnable and still in use so I guess everything works fine. Maybe try to do it this way? 

If I am wrong true me up guys please 😊

[VASSILY]

Thanks Mateo. I am not very skilled in using bots. Still I am trying to understand and aply how to run a pdf file through bots 😅...  God knows I´m trying

[Lindsey_Lindow]

What expression does your bot use to look at the highest row and add +1 to the value?  I use a bot for the same purpose, but a few times it didn't work and the cell was left blank (no errors in the automation monitoring pane, so I never found out what went wrong).

[VASSILY]

CONCATENATE(“D”, RIGHT((“0000” & ([_RowNumber] -1 )), 4))

If the formula sometimes fails is due to erasing of some rows. You have to erase every referenced row in every table.