Forum Discussion

DeepaPrashant1's avatar
DeepaPrashant1
Qrew Cadet
2 years ago

Table relationship with a formula field in child table

I need some help thinking through this architecture.

My table 'Asset form' collects data related to company assets. My other table, 'Asset Approvals' will collect approvals for these asset changes.

Now the data for these assets ('Asset form') is going to be collected via GRID EDIT mode; so a bunch of Assets will be clubbed together and will require only one approval. I have created a formula field 'Change Req ID' that is a concatenation of person creating the request and date and time. So ideally I want to create a one to many relationship between the 'Asset Approvals' table and the 'Asset form' table but link them using the 'Change Req ID.'

So from the 'Asset Approvals' form I would like a drop down of all 'Change Req IDs' (which would comprise of many Asset form records), choose one and upload my approval attachment. This should then show up on all Asset forms as an attachment that has that 'Change Req. ID'.

What's the best way to achieve this?

Thanks

deepa



------------------------------
Deepa Prashant
------------------------------

20 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 years ago

    I would suggest one of two routes: 

    Route (1): 

    Rethink the idea of how you're grid edit'ing to enter the asset forms, and put them into a 'Asset Form Request' Container that is a record that can bind all of your Asset Forms underneath an umbrella record. The structure would be Asset Form Requests have many Asset Forms. You can set it up that users add a record and then use an embedded grid edit form to enter each Asset form. 

    Doing it this way means that its guaranteed that all of the Asset forms are bound together. You would then make the 'Approval' just a subset of fields as part of the Request record instead of another (3rd) module. You can have it such that the User can submit the Asset Form Request for approval and log those fields, then have the approver sign off and attach it in field on the Asset Form Request and call it a day .

    Route (2): 

    When you save the grid edit and commit the Asset Forms, have a Pipeline pick up those events, and then if the Approval Record for that Change Req ID doesn't exist already, create a new record in your Approvals table. In that table, set the key field to be the Change Req ID. 

    When you make the relationship, instead of letting Quickbase create a new 'Related' field as the foreign key, set the foreign key to be your Change Req ID field so that QB will bind it together through the standard relationship. 

    I would raise one concern with going Route (2) in that how do you alert the approver(s) that they need to sign off? Are your uses able to enter via grid edit and then make changes afterwards that might impact the approval? You will likely need some way to have the user 'submit' the handful of Asset Forms when they're ready, which would be better under the structure suggested in Route (1). 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • DeepaPrashant1's avatar
      DeepaPrashant1
      Qrew Cadet
      2 years ago

      Thank you Chayce!

      I definitely like route 1 better. From the parent table, 'Asset Form Request', when I edit the embedded report in Grid edit mode:

      1. The records appear to link up only when I add the 'Record ID' of the current 'Asset Form Request' in the Embedded table. It doesn't automatically assume I am creating forms linked to this record in the Asset Form Request record. Is that correct or am I missing some settings?

      2.  Also, until I save the 'Asset Request form' record, it doesn't have a  record ID. So how do I ensure I can create within the Embedded form (Asset form) records for the related Asset Request form?

      I am definitely missing something.

      Deepa



      ------------------------------
      Deepa Prashant
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        2 years ago

        If you're leveraging a basic relationship between Asset Request form and the Asset Form then you shouldn't need to do anything additional. 

        From the actual 'Form' or the Asset Request Form Table - you would just need to put the Report Link field that Quickbase generates by default on the form and have it set to a simple report that your users can grid edit. In the form elements - you can then make the element display as a grid entry and then when users are editing the native form, or when they're adding a new record Quickbase will automatically relate the new records as part of that functionality. 

        **I forgot to mention this - but this method only applies on the Legacy forms. The new forms don't have the same grid edit functionality as it stands right now to my knowledge so this technique is something the Legacy forms would be needed for. 



        ------------------------------
        Chayce Duncan
        ------------------------------
  • DeepaPrashant's avatar
    DeepaPrashant
    Qrew Member
    2 years ago

    As a follow-up to this question, the embedded grid edit report that shows up displays only 4 empty rows. We have use cases where the analyst needs to fill in 200+ rows and would like the ability to copy-paste that many rows into the embedded grid edit report. 

    See above empty report. So when I try to copy-paste more than 4 rows, only 4 rows of data sticks. This would make it very laborious to copy 200+ rows as it would have to be done 4 rows at a time. 

    Is there a work around for this?

    Thanks!

    Deepa

    • MikeTamoush's avatar
      MikeTamoush
      Qrew Elite
      2 years ago

      I have a solution we use but it is not great (but way better than 4 at a time).

      We click on the very left cell (with no column heading) and drag and highlight the 4 rows, right click and say Insert Blank Rows. Now you have 8 rows. Do the exact same thing again with all 8 rows. Now you have 16. Once more gives you 32....and so on. You can somewhat quickly get yourself to to your number. You just want to be sure to have MORE rows than you need. And I would test to see if there are any quirks when you get up to a high number. We have only imported maybe 30 or 40 max, so I am not sure if anything funny happens with 200. Keep in mind when copy/pasting you need to use all mouse commands (dont try ctrl c/v on keyboard, it wont work) and use 'Paste Special'.

      However, if you have that many rows, I would suggest trying to use the import function on the table home page. The trick will be the user will need to know to add a column with the related parent record ID....