Discussions

 View Only
Expand all | Collapse all

Create a field that incrementally counts based on unique values in another field

  • 1.  Create a field that incrementally counts based on unique values in another field

    Posted 03-30-2022 10:12
    I have a situation where I'd like to automatically create sequential [invoice] numbers in a parent table based a [new,] unique value in a child table. 

    If I was interested in approaching this using QB System Variable(s) and/or Javascript/Formula-RTF (I am a novice in both), how would I go about this?

    Thanks in advance

    ------------------------------
    Ken Hillyer
    I support people who cure cancer
    Alliance for Clinical Trials in Oncology Foundation
    Boston, MA
    ------------------------------


  • 2.  RE: Create a field that incrementally counts based on unique values in another field

    Posted 03-30-2022 13:54

    The easiest way I can think of to do this is with a formula query similar to this:

    var text tableID = "DBID_TABLE";
    var text query = GetRecords("{3.LTE." & [Record ID#] & "}AND{FID.EQ.'Value'}", $tableID);

    Size($query)

    Where DBID_TABLE is the table ID of the child table, FID is the Field ID of the Unique field mentioned in the child table and Value is the unique value you're using as your counter.

    I used something similar to track the number of records created on a given date:

    var recordlist query = GetRecords("{3.LTE." & [Record ID#] & "}AND{1.EQ." & ToDate([Date Created]) & "}");
    var text recordCount = ToText(Size($query));

    Year(ToDate([Date Created])) & Month(ToDate([Date Created])) & Day(ToDate([Date Created])) & PadLeft($recordCount, 4, "0")



    ------------------------------
    Paul Peterson
    ------------------------------