I am trying to create a ticket number that seems very basic, but part of it is not working well. The ticket structure is simple, the last two digits of the year, the two digit month, two digit day plus a 4 digit number that resets to starting with 1 each day. The first three parts are very straightforward, but I am not able to find a way to reset the four digit number back to 1 at the start of each day. What are your suggestions?------------------------------Paul Peterson------------------------------

Paul, You are trying to make a smart number for the ticket system. The first parts are easy as you stated but the final four digits have to be tracked separately against each individual day. In the Quick Base world the Ticket would have to be related to another table of Days so that you can calculate there what the unique serialization is for that day each time a ticket is created. Here a link to an article that discusses creating a Months Table for analytics purposes. https://community.quickbase.com/blogs/tom-leverone1/2020/09/23/quick-base-analytics-improve-your-monthly-insights However even if you do this, you still have to have way to calculate the index for each record that day. Assume that you have three tickets on the 1st of Jan Record ID# 4568 Record ID# 4569 Record ID# 4570 The Record ID#s are sequential, because they would have been created in that order on the 1st but for your application you need to calculate that RID 4568 is 0001 on that day and so on. Every time you relate a ticket to that day, something must calculate that value. A Summary field between the Ticket and the Days table would dynamically tell you the number of Tickets. If you use an Automation or Pipeline to capture that on Record Add and the write that value to the Child Table you would then know its number for the day. Latency could be a problem if you have lots of tickets. If the Ticket with RID 4569 was created before you have captured and written the index to 4568 then both of them might be marked as #2 and you would not capture #1. This might work, but I think you would be better off doing a custom solution with some PHP or other scripting language would actually look at the Record IDs and the [Date Created] and then calculate the unique index of the day. ------------------------------Don LarsonPaasporterWestlake OH------------------------------

Paul, Building on what Don posted above, you can have a relationship where a table is loaded up with all the days for the next 10 years or so related to your tickets. You can then have a summary field to some the number of tickets for that day look that up down to the ticket and then create a snapshot value to freeze the value. That Snapchat field will then be sequential for each day. ------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Paul, Mark's idea is perfect. Wish I had thought of using the Snapshot on the Summary field. Do what he said and you will have a solution with no external code required.------------------------------Don LarsonPaasporterWestlake OH------------------------------

I finally cleared enough other tasks out of the way and had a change to return to this. I don't think I explained my objective clearly enough. I am not looking to keep track of the tickets created each day. The objective is to assign a number to each ticket created that day Starting with 0001 and incrementing for each successive ticket and resetting to 0001 the next morning.------------------------------Paul Peterson------------------------------

Paul, did you try this? I see that my Siri dictation was not the best so I have edited it below. Building on what Don posted above, you can have a relationship where a table is loaded up with all the days for the next 10 years or so. Make a relationship back to ticket with a formula date field equal to your date field. Note that it may not let you use the [Date Created] field as a basis for the formula for the date so you may need an automation or a form rule to populate the date field with today. Or i guess just a date field which defauts to Today would be good enough.You can then have a summary field to total the number of tickets for that day look that up down to the ticket and then create a snapshot value to freeze the value. That Snapchat field will then be sequential for each day. So Ticket #1 for a given day will get a snapshot value of 1So Ticket #2 for a given day will get a snapshot value of 2So Ticket #3 for a given day will get a snapshot value of 3Here is some help on setting up snapshot fields.https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html ------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Reset Value every day? | Qrew Discussions

DonLarson
Qrew Elite
5 years ago
Paul,

You are trying to make a smart number for the ticket system. The first parts are easy as you stated but the final four digits have to be tracked separately against each individual day. In the Quick Base world the Ticket would have to be related to another table of Days so that you can calculate there what the unique serialization is for that day each time a ticket is created.

Here a link to an article that discusses creating a Months Table for analytics purposes.

https://community.quickbase.com/blogs/tom-leverone1/2020/09/23/quick-base-analytics-improve-your-monthly-insights

However even if you do this, you still have to have way to calculate the index for each record that day.

Assume that you have three tickets on the 1st of Jan

Record ID# 4568

Record ID# 4569

Record ID# 4570

The Record ID#s are sequential, because they would have been created in that order on the 1st but for your application you need to calculate that RID 4568 is 0001 on that day and so on. Every time you relate a ticket to that day, something must calculate that value.

A Summary field between the Ticket and the Days table would dynamically tell you the number of Tickets. If you use an Automation or Pipeline to capture that on Record Add and the write that value to the Child Table you would then know its number for the day.

Latency could be a problem if you have lots of tickets. If the Ticket with RID 4569 was created before you have captured and written the index to 4568 then both of them might be marked as #2 and you would not capture #1.

This might work, but I think you would be better off doing a custom solution with some PHP or other scripting language would actually look at the Record IDs and the [Date Created] and then calculate the unique index of the day.

------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
- MarkShnier__You
  Qrew Legend
  5 years ago
  Paul,
  
  Building on what Don posted above, you can have a relationship where a table is loaded up with all the days for the next 10 years or so related to your tickets. You can then have a summary field to some the number of tickets for that day look that up down to the ticket and then create a snapshot value to freeze the value. That Snapchat field will then be sequential for each day.
  
  ------------------------------
  Mark Shnier (YQC)
  Quick Base Solution Provider
  Your Quick Base Coach
  http://QuickBaseCoach.com
  mark.shnier@gmail.com
  ------------------------------
  - DonLarson
    Qrew Elite
    5 years ago
    Paul,
    
    Mark's idea is perfect. Wish I had thought of using the Snapshot on the Summary field.
    
    Do what he said and you will have a solution with no external code required.
    
    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------

Forum Discussion

Reset Value every day?

Related Content

Find and Replace Field Values

Using AND with Null values

Jinja If statement equals field value

Grabbing value between 2 fields

Color Coding based on field value