Expression Help?

So, I’m not an expert when it comes to expressions. I have an expression that I want to write, but unsure how to do it.
I have a Tulip app that needs to read the most recent or highest serial number, then add to it a variable an operator inputs. It will then store those newly created serial numbers in the Tulip table.

So to visualize things, here’s the Tulip table:

And here’s the test app:

When the button is pushed, I would like the expression to find the highest serial number in the “serial number” column, then add 1 for each number in the “Input# of things to build” and create a list of new serial numbers to be added in the “serial number” column in the Tulip Table.

EX. if an operator is building 10 new things, and the last serial number is “SP12345” then once the button is clicked, 10 new serial numbers will be added to the “Serial Number” column:

Is that even possible?

Hi @jasonh,

This definitely should be possible but I might approach it a little differently.

The first challenge you’re going to face is that the Serial Number column is of data type text, not an integer. This means that the MAX() operator doesn’t mean very much in this context. However, you could work around this a couple ways. First, if your Serial Number is always incrementing, you can infer that your last created Serial Number is your max value.

This should be easy to find - just write a table query to return records sorting by date created descending and limit the output to 1. You can then use an aggregation for Mode on the Serial Number column to get a tangible value within your application.

From here, the input widget (I’ll call it Build Quantity) you have really indicates how many times your app will need to loop to create records. So if you set a variable called Index to a default value of 1, when you press the button to generate you can go to a looping step. On step enter, check to see if Index > Build Quantity. If it’s not, create a new table record where the ID is an expression that takes the table aggregation, strips the SP from the ID, converts to an integer, adds one, then adds the SP back to the ID field. You can then increment your Index and transition back to the same step.

Once the condition fails, you’ve created 10 records and can move to the next step.

There’s a couple different design decisions you may need to make depending on how many people could be running this at once, if the parts need to be sequential, etc. but these would be the general steps.

Let me know if that makes sense!

Hi @Grant
Thanks for the reply!

Our serial numbers are a combination of letters and numbers, so must remain as text. I’ve created a table query to sort by serial number (z to a) so that should sort the latest serial number to the top.

Our build process happens in groups of 10 (sometimes less, sometimes more). Looping makes sense, but we need to have a set of X numbers generated and saved in the table.

To explain why, we generate serial number labels based on the group of parts the operator will be making. Right now this is a paper process where the operator writes down the serial numbers, then physically walks to a printer to input the numbers and print the labels.
In the future we plan on integrating the printing function into Tulip, but for now it will remain a manual process.
The operators do builds in groups of 10, so we need to stick with that until we can gradually change.

The aggregation that removes the text, converts to an integer, increments then concats the text back in is great! The generating X# of serial numbers is the toughest part right now…

Hi @jasonh,

You can definitely create a variable number of new table records together but it will need to happen via a loop as we only support actions creating one table record at a time (unless you used X number of placeholders but that wouldn’t truly by dynamic).

The issue I wanted to highlight was if you had multiple people creating records at one time, you have to make a decision on how you want to organize them. You could theoretically have:

  • SN1 (Operator A)
  • SN2 (Operator B)
  • SN3 (Operator A)
  • SN4 (Operator B)

or you could build in protection where operators reserve the next block of serial numbers:

  • SN1 (Operator A)
  • SN2 (Operator A)
  • SN3 (Operator A)
  • SN11 (Operator B)

To accomplish the first, you’d just want to build a table record based on the returned value from the aggregation. To accomplish the second, you’d probably want to use a second table that tracks the current serial number and just increment by the reserved part count immediately so that the next user takes the next block.

Does the looping step make sense? You would just keep using step open triggers to fire actions to create a new table record based on some dynamic value (either the aggregation or some variable counter depending on which approach you take).

@jasonh As your description for SN,your expression should like below:

‘SP’ + totext(texttointeger(right(@Variable.CodeDesc , 5)) + 1)

The result of each step as below:

SP12345 right(@Variable.CodeDesc , 5) => “12345” string
texttointeger(right(@Variable.CodeDesc , 5)) => 12345 int
texttointeger(right(@Variable.CodeDesc , 5)) + 1=>12346 int
totext(texttointeger(right(@Variable.CodeDesc , 5)) + 1) =>12346 string
‘SP’ + totext(texttointeger(right(@Variable.CodeDesc , 5)) + 1)=> “SP12346”

if you need generate 10 or more SN one time,you need combine with customer widget for loop 10 times

wish it will be helpful!

1 Like

Hi @jasonh,

to Auto-Increment a Table Value (also mixed with text) is an often requested funktionality.

Please have a look if the solution in this thread helps:

Hi everyone,

I wanted to take a quick minute to thank you all for helping me with this problem. As someone who has very little programming experience, I’m struggling with understanding and completing expressions and triggers. I’m going to re-visit some of the Tulip University sessions on the Table Queries, Aggregations and the Expression editor.

My hope is in the near future all of this will make more sense and I’ll have a better handle on things.