Every time a user makes an entry into my app I want to add 1 to the “job number” from the previous entry.
I have tried using array formulas but I cant work out how to say + 1 to the row above (easy if you are adding stuff in the same row).
Whats the best way to do this? I looked for a way of sending in a formula as part of the entry but unless I am missing something its not there.
Thanks!
Is the “job number” simply a reference ID code? If so, then when users make a entry to your app (via form button), it will then create a new row in your sheet.
Two ways depending where you need the job number value.
If you need it in the spreadsheet, then use an arrayformula:
In the sheet you can use either the rows(range) formula or you can do a counta(range) formula
If you only need the job number in the app and not as a value in the spreadsheet (thereby giving you less lag), you might be able to leverage the new rollup column in the data editor to count the number of records in the sheet. Then, use a template column to format that number however you wish.
Not 100% if this would work as I haven’t played with forms much but could you have a function in the sheet already?
Something like this in a2:
=if(b2=“”,””,a1+1)
You could also play with the row() function and add whatever is needed (or subtract) based on the row. Like
=if(b2=“”,””,Row()-1)
This way would use the current row number and takeaway 1.
Assuming that you want to put the array formula in row 1, and something is in column A that indicates this is a valid entry, and you start with a job number of 1 this should work for you.
={"JobNumber";ARRAYFORMULA(IF(LEN(A2:A),row(A2:A)-1,""))}
I don’t know if you are looking for an actual incremental value or just a unique Job Number ID. Everybody else has great ideas. I just want to add that if you are looking for a unique identifier, take a look at the Unique Identifier Special Value component. This will give you a unique value for each new entry and won’t be an issue if you ever sort your sheet.
https://docs.glideapps.com/all/reference/special-values/unique-identifier
@Jeff_Hager makes an excellent point. Ask yourself what you are using the job number for, and also think about what happens in the future if you allow deletes and then want to remove those blank rows. Overall using a row number for any kind of linking and relationships is a BAD idea.
This sounds very sensible. Will give it a go - thank you everyone!