Creating a Unique Reference - From Date and Numbers to Text

Support
1

Hi.

I am trying to figure out how to create a unique reference for data items based on a client request. The request is as follows:

The data item reference should include the date, the asset code, the staff code, and the sequence for the day e.g. 07042023-05-12-01 means on 7th April 2023 an order for item 5 was created by staff number 12 and it was the 1st order for that day.

I, therefore, need to find a way of resetting the order counts per day, padding the sequential ID of assets and staff if less than 10, and combining these date and number fields into an any-text field. If the count for the day is less than 10, I also need to pad it with a zero.

Is this possible on Triggre and how would I go about it?

2

Hi Isaac,
From my own experience I could provide some suggestions that may work.
In my applications padding with prefix zeros is quite commonly used. Given any numeric value I calculate the padded text value as follows:

CUT ‘00000’ FROM (REMOVE ‘.000000’ FROM (REMOVE ‘,’ FROM (MAKE n A TEXT) ) ) TO END +
REMOVE ‘.000000’ FROM (REMOVE ‘,’ FROM (MAKE n A TEXT) )

Where:

  • ‘00000’ is any number of prefix zeros (or other characters) that equals the desired TEXT length minus 1.
  • n is any numeric value you want to convert to padded text
  • Local settings are set to US number format, for European format just switch commas and periods in the queries above.

So in your case, with a desired text length of 2 this would be:

CUT ‘0’ FROM (REMOVE ‘.000000’ FROM (REMOVE ‘,’ FROM (MAKE n A TEXT) ) ) TO END +
REMOVE ‘.000000’ FROM (REMOVE ‘,’ FROM (MAKE n A TEXT) ).

If your numeric value already has a text format, you could of course use the NUMBER OF CAHARACTERS OF A function:

CUT ‘0000’ FROM (NUMBER OF CHARACTERS OF a) TO END + a

For the daily counter I see two options:

Option 1:
Abandon the sequence value on data level and instead create a data-item that creates your own counters on organization or application level and create a flow part to:

  • Retrieve the current value of the counter and add 1
  • Save the new value back into the counter
  • Use the new counter value to add it to your ID string

Then create an Automation Flow that resets the counter value to 0 each day at midnight.

Option 2
Based on the date value (or its’ text equivalent) you could determine the new counter value by either:

  1. counting the currently saved requests with the same date/textvalue (new value would be count result plus 1) OR
  2. you could determine the highest value of the counter in a selection of requests that match the current date. You should then add an numeric attribute to your data-item for the counter, since that saves you building a complex query and will be more efficient when executed.

Both options are implemented using a search within a flow part for Requests where the text for the date value (e.g. ‘20230407’ matches the first 8 positions of the Request ID, using CUT [RequestID] FROM 1 TO 8. Then you can use either COUNT ITEMS IN LIST A and add 1 to the result, or MAXIMUM OF A, where ‘A’ is the Counter attribute in the Requests List you have selected, and again add 1 to the result.

None of these methods of course are as solid as a sequence. There is always a risk that two users add a Request at the same time and thus create the same value for the counter, resulting in a duplicate value. There is a method to counter this, using an Automation Flow on a queue for creating the new records and/or ID’s, where the queue records do get a sequence and the flow handles the queue entries in the order of the sequence. With many users and requests there is a slight chance that this can cause some delay in processing new records on busy moments, but it does ensure data-integrity. Please note that this approach also increases the number of actions needed to create a record.

Hope this helps.

4 Likes
3

Hi.

Thanks for the detailed response. Let me try this.

Regards
Isaac

1 Like