# Creating a Unique Reference - From Date and Numbers to Text

**URL:** <https://community.triggre.com/t/creating-a-unique-reference-from-date-and-numbers-to-text/297>\
**Category:** Support\
**Created:** [April 7, 2023, 5:01am UTC](https://community.triggre.com/t/creating-a-unique-reference-from-date-and-numbers-to-text/297 "2023-04-07T05:01:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![isaac](https://avatars.discourse-cdn.com/v4/letter/i/8e8cbc/32.png) [@isaac](https://community.triggre.com/u/isaac)\
**Post date:** [April 7, 2023, 5:01am UTC](https://community.triggre.com/t/creating-a-unique-reference-from-date-and-numbers-to-text/297/1 "2023-04-07T05:01:32Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![sylvester](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.triggre.com/sylvester/32/787_2.png) [@sylvester](https://community.triggre.com/u/sylvester)\
**Post date:** [April 7, 2023, 12:18pm UTC](https://community.triggre.com/t/creating-a-unique-reference-from-date-and-numbers-to-text/297/2 "2023-04-07T12:18:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![isaac](https://avatars.discourse-cdn.com/v4/letter/i/8e8cbc/32.png) [@isaac](https://community.triggre.com/u/isaac)\
**Post date:** [April 8, 2023, 7:08am UTC](https://community.triggre.com/t/creating-a-unique-reference-from-date-and-numbers-to-text/297/3 "2023-04-08T07:08:34Z")

</div>

Hi.

Thanks for the detailed response. Let me try this.

Regards  
Isaac