Forum Discussion

Jan-WillemSpeck's avatar
Jan-WillemSpeck
Qrew Assistant Captain
8 years ago

Auto create n records in a table

Hi,
I'm considering to build a database around DDI management (Telephone numbers).
We manage real large ranges of blocks of 10's, 100's,  1000's even 10000's 
I like to keep track on stuff like:
  • What location is the number used?
  • What is the full number?
  • Who is the user related
  • What forward is set on the number if any?
  • What other settings?
  • on what type equipment is the DDI terminated?
  • etc etc.
My current challange is how to create a new range of DDI's.
For example we open a new location with main number 01-22-3000 and with a block of 1000.
In other words I'm looking for  a simple and user friendly way to auto create 1000 records in a table starting at 01-22-3000 upto 01-22-3999.

So I'm thinking to create a table that holds all main numbers (start numbers) and block sizes. When an new entry is created in this table it would trigger a technique to auto create 1000 new DDI-records in another table where the relevant details can be managed by the users.

My intiuition tells me to look for a sollution in QB actions and or Webhooks but I don't really know where to start and what the best technique would be.

Any suggestions and in particular links to examples are welcome
APIs and custom code
Ask about Quickbase functionality
Data import and export
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    If you need to make these blocks 1,000 at a time, you can create a table with exactly 1,000 records in it from record ID 1 to 1000. Call this the 1 to 1000 table

    Then make a single admin record in a new table where you specify the prefix and the start number start number.  So you enter 01-22 and in a separate numeric field enter 3000.

    The Record ID of that admin record is Record ID# 1.

    Make a relationship to the 1- 1000 table based on a numeric formula field called [Link to admin table] and it will have a formula of 1.

    Lookup the start prefix  and start number to the records in the 1-1000 table. 

    Make a formula field to calculate the value of the DDI to be created.

    it would be like

    [Admin start prefix] & "-" right("0000" & totext([Start number]-1+[Record ID#]),4)

    Check that the formula looks correct :)

    Next make a table to table saved import to copy the records from the 1-1000 table into your target table.  Take note of the ID# in the URL.  It will be ID# 10 for the first one you create.

    Then make a URL formula button to copy the records across.

    var text Import  = urlroot() & "db/" & [_DBID_my_ddi_table] & "?act=API_RunImport&ID=10";
    var text DisplayAdmin = urlroot() & "db/" & dbid() & "?a=dr&rid=1";

    $Import  
    & "&rdr=" & URLEncode($DisplayAdmin)

    I suggest that you make a report link field on the Admin record to show the newly created records.

    Let me know how you make out as you work your way though the steps.
  • Jan-WillemSpeck's avatar
    Jan-WillemSpeck
    Qrew Assistant Captain
    8 years ago
    Yep.. I got the idea..

    I would need to look to use some fields as 'variables' to tweak the starting numbers n the formula's
    Similar tables could be created to provide the templates for 10's or 100's blocks and by repeating an import I can create ie. x*10 = x0 DDI's quickly. at l east much quicker than record by record :-)

    This is something I can spend some nightly hours on and this will help me forward.
    Thanks for the suggestion. Will keep you posted intime.

    JW
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    OK, great. Obviously the "magic" here is the concept to import X records from another table as a table to table copy, as that can happen with a click and they populate instantly.