Skip to main content
Question

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

  • March 30, 2022
  • 0 replies
  • 68 views
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
------------------------------
This topic has been closed for replies.

  • PaulPeterson1
  • Quickbase Alumni
  • March 30, 2022

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
------------------------------
Terms & ConditionsAccessibility statement