Discussions

 View Only
Expand all | Collapse all

Table relationship with a formula field in child table

  • 1.  Table relationship with a formula field in child table

    Posted 17 days ago

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


  • 2.  RE: Table relationship with a formula field in child table

    Posted 16 days 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
    ------------------------------



  • 3.  RE: Table relationship with a formula field in child table

    Posted 16 days 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
    ------------------------------



  • 4.  RE: Table relationship with a formula field in child table

    Posted 16 days 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
    ------------------------------



  • 5.  RE: Table relationship with a formula field in child table

    Posted 16 days ago

    Thanks Chayce.

    I am using the new form and that does make things more time consuming for me as I am still figuring my way around those. It does allow Grid edits for embedded reports. But doesn't appear to automatically link the Asset Form Request record to the new asset forms via Grid Edit. I need to have to use the drop down in the Asset forms Grid Edit and select the Related Asset form and then the report shows up correctly.

    Also now my app has both legacy forms and new forms making it inconsistent. Any way of switching back to legacy?



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



  • 6.  RE: Table relationship with a formula field in child table

    Posted 16 days ago

    If you want to keep the new forms, I have a quirky idea that you could try to link them up automatically but if you're already on the fence between the two form types, I would personally switch over to the legacy forms so that this specific workflow is a little more seamless/user friendly with the grid edit. 

    You can switch your users back to the legacy forms in the form settings for each table. You'll have to make sure that any changes/updates that were made in the new forms are recreated in legacy, I'm not sure how difficult that would be for you though based on how you've used them thus far. 



    ------------------------------
    Chayce Duncan
    ------------------------------



  • 7.  RE: Table relationship with a formula field in child table

    Posted 15 days ago

    Just chiming in on that Grid Edit issue where the current behaviour in new style forms allows the user to create children which are not linked to the parent or I guess if you make the [Related Parent] field required it would require the user to choose the parent that they are sitting on.

    One option is to set the form properties in new style forms to disable grid edit. Then create a legacy form which is super mini in size. Just basically a heading which identifies the parent and an embedded grid edit report.

    Then make a URL formula button for the new style form that would re-display the record Edit Mode on the Legacy Mini "Grid Edit" form.Then the user would do their data entry in grid edit mode and SAVE and it would re-display on the New Style form.

    That would allow you to maintain consistency of look of the new style forms which in the long term is a better user experience but provides an acceptable workaround to Grid Edit where the user basically hits a button to Grid Edit.

    Eventually one would hope that the New Style forms will mimic the Grid Edit behaviour of Legacy forms but until then this is probably an acceptable work around.  



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Table relationship with a formula field in child table

    Posted 15 days ago

    Thank you so much Mark and Chayce for your suggestions. 

    For now, I decided to take a simpler rule and added a few lines of instructions and will have the analysts first create the Asset Request form (save it) and then add the records via Grid Edit in Asset forms with Related Asset Request a required field. 

    I decided to keep the new form as I do like the layout with the Grid edit report showing to the right in the Asset Request form.

    Thank you so much for your help!



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



  • 9.  RE: Table relationship with a formula field in child table

    Posted 15 days ago

    Jus re-reading Mark's solution. Wow! You do think outside the box Mark. Having a form within a form, that's brilliant!



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



  • 10.  RE: Table relationship with a formula field in child table

    Posted 15 days ago

    ... Well not really a form within a form, just clicking the button to re-display the same record on an alternative mini Legacy form.  

    URLRoot() & "db/" & dbid() & "?a=er&dfid=11&rid=" & [Record ID#]

    where in the example above, the form ID# of the Legacy form is #11.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Table relationship with a formula field in child table

    Posted 15 days ago

    Got it.

    Thanks!



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



  • 12.  RE: Table relationship with a formula field in child table

    Posted 11 days ago

    Hi Mark,

    I tried what you suggested, but until I save the Asset Request form and a record ID is created for the Asset Request, the asset form details in the legacy Grid Edit report do not get attached to the newly created Asset Request form Record ID#.

    It appears I have to first save the Asset form request and then pull up the grid edit asset forms and fill in the details for it to get attached to the asset request form. I can't create the Asset request form and add the asset details in one go.

    Am I missing anything?



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



  • 13.  RE: Table relationship with a formula field in child table

    Posted 10 days ago

    OK, so to be clear about what we are trying to solve for is that unfortunately at this time New Style forms does not allow for child records to be added in Grid Edit mode without forcing to the user to manually select the Parent in Grid Edit.  In Legacy forms, one can create Grid Edit (connected) Children either in Add Mode or in Edit Mode. 

    The ask is therefor to be able to create a button in Add Mode (but which will also work in edit mode) which will auto save the Parent and re-display the newly created record on an alternate "Legacy style" mini Grid Edit form, so children can be auto attached.

    We will need to use a formula Rich text field (not formula URL).  

    var text RID = If([Record ID#] > 0, ToText([Record ID#]), "%%rid%%");
     
    var text URL  = URLRoot() & "db/" & Dbid() & "?a=er&dfid=2&rid=" & $RID;
     
    "<a class='SaveBeforeNavigating' data-replaceRid=true style=\"text-decoration:none; background: #1ba802; border-radius: 5px; color: #ffffff; display: inline-block; padding: 0px 0px 0px 0px; width:100px; text-align: center; text-shadow: none; border: 2px solid #1ba802; font-size: 12px \"href='"
    & $URL
    & "'>Grid Edit</a>"
    // change the form # to the Legacy form &dfid=2 


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 14.  RE: Table relationship with a formula field in child table

    Posted 10 days ago

    Mark,

    This was perfect!! Love it.

    Thanks!



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