Forum Discussion

KenHillyer's avatar
KenHillyer
Qrew Member
3 years ago

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

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
------------------------------
formula queries

1 Reply

Sort By
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    3 years ago

    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
    ------------------------------