Discussions

Expand all | Collapse all

table relationships

  • 1.  table relationships

    Posted 20 days ago
    I am not sure if what I want to do is possible in QuickBase.  I am building a database to track the handling of overpayments.  That table is the "parent".  I hoped to build relationships to two sub-tables.  One is for recording actions taken.  The second one tracks balance changes.  (i.e. the original balance being pursued is $10,000...a month later, it is $9,000).  If I were just building the database on a moving forward basis, I don't think I'd have any issue.  However, I am trying to import historical data.  My relationships don't work because the Record ID in the parent table does not tie to anything in the child tables.  I used a formula to concatenate fields and build a true record identifier, but I am unable to use that as the Key since it is a formula.  Is there any way to backload data and build the relationships between these tables?  Admittedly, I am newer to QuickBase, but so far, the tutorials I've watched don't show the functionality I'm looking for.

    ------------------------------
    A Evans
    ------------------------------


  • 2.  RE: table relationships

    Posted 12 days ago
    If I'm understanding correctly, you should be able to achieve this by doing a couple of things:

    1. Create another field in your table that will be your key field (a data entry field, not a formula)
    2. Copy the value of your formula field into this new field for every record
    - Not sure how much data you have but if small you could do this in grid edit, if you have a lot of data just dump the table out with RID, and import your formula key column into the data entry field
    3. change your key to the non formula field
    4. On a moving forward basis set up a form rule in that table that on save, copies the value from your formula field to the key field

    ------------------------------
    Charlie Murawski
    ------------------------------



  • 3.  RE: table relationships

    Posted 11 days ago

    Hello! Thank you for responding.  I was able to make all of the relationships, load my historical data, and see the proper child table data from the records on the parent table.  But, now I'm stuck trying to implement step 4 in your response.  The formula in the Concatenated ID field calculates as users key in a new overpayment record.  I need that formula result to copy into the Deficit Record ID field to become the Key field on the newly added record. 

     

    I tried using a Dynamic Rule, but the Deficit Record ID is not populating.  Therefore, I receive an error when I try to save the record because the Deficit Record ID is blank (and it is the required Key field).  My rule is shown below.

     

     

     

    I also tried to set up an automation, but I was unable to select my Concatenated ID from the Select field list shown below.  What am I doing wrong?

     

     

    Thank you, again, for your help!

     

    Alexis Evans J

     

     

    Proprietary






  • 4.  RE: table relationships

    Posted 11 days ago
    In your form rule try using "When the record is saved".

    ------------------------------
    Charlie Murawski
    ------------------------------



  • 5.  RE: table relationships

    Posted 11 days ago

    I'm sorry, but where am I supposed to use that?  I cannot find it in any of the available drop downs.  If I need to key it somewhere, I don't know where. 

     

    Alexis Evans J

     

     

    Proprietary






  • 6.  RE: table relationships

    Posted 11 days ago
    This option in the form rule "when" dropdown:



    ------------------------------
    Charlie Murawski
    ------------------------------



  • 7.  RE: table relationships

    Posted 11 days ago

    That was so easy!! I'm sorry I was a dummy and overlooked that.  Thank you so much for your help!

     

    Alexis Evans J

     

     

    Proprietary






  • 8.  RE: table relationships

    Posted 4 days ago

    I have made a lot of progress with my database, but now I'm stuck on a new piece. Balances being collected are in a parent table named Overpayments.  There is a Balance Changes table where users will record any changes to the balance due (whether + or - ) as of a certain date.  I added a field in the Overpayments table named "current balance due".   I would like to update that field with the newest balance due whenever a change is added.  Based on some other discussions threads I found, I tried adding a summary field to my existing relationship and named it "Maximum As of Date".  However, how do I update the Current Balance Due with the specific amount associated to that "maximum As of Date?"

     

    I hope I make sense.  Thank you for your help!

     

    Alexis Evans J

     

    Proprietary






  • 9.  RE: table relationships

    Posted 4 days ago
    You're about 1/2 way there! Take your summary field you created and in that same relationship create a lookup field with it. Then you can create another summary field with the condition to only summarize where as of date = maximum as of date lookup.

    I hope that makes sense!

    ------------------------------
    Charlie Murawski
    ------------------------------



  • 10.  RE: table relationships

    Posted 3 days ago

    Hello again!  I'm getting closer!  The last piece is still not working for me.  Right now, here are my current dynamic forms rules.  I tried adding a 4th rule that would update the Current Amount due whenever the balance was not cleared, but a balance change was added.  However, it didn't work for me.  I'm not sure if I'm just setting it up incorrectly or if it is being blocked by one of the prior two rules.  Currently, the first three rules below are working as expected.  Thank you in advance!

     

     

     

     

    Alexis Evans J

    Phone (717) 671-5259

     

     

    Proprietary