Can you get get a url formula in a button to check a child table if an associated record exists and if not create it?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have a master table called table 1 and a child table called table 2.

For every record in table 1, a record in table 2 must exist and I know from searching, I cannot create a child from the master.

What i'd like to do is to have a URL formula button that checks if the associated record exists and if it does, open it and if it doesn't, create it.

I have the formula below for the opening of the record -

URLRoot() & "db/" & [_DBID_TABLE_1] & "?a=er&dfid=17&rid="& [Candidate record ID#]

I just need some help with the syntax for checking if it exists first.
Photo of Hirsty2000

Hirsty2000

  • 30 Points

Posted 3 years ago

  • 0
  • 1
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
The easiest way to check to see if it exists would be to do a Summary Field through the relationship and just count the number of Child Records. I'm curious, however, if you can provide a bit more context into the issue. You mentioned that in searching, you found that you cannot create a child from a master record, which isn't actually correct. If you can provide a bit more context about what your process is or the business reasons as to how the two tables relate to one another, I may be able to provide a better answer for you.
Photo of Hirsty2000

Hirsty2000

  • 30 Points
Hi Blake.  This is a Candidate Tracking tool for a recruitment team.  The master record (in table 1) captures all of the personal data.  Child record (in table 2) captures all of the compliance data.  For every Master record in table 1, I have to have a unique child record in table 2.

To cut down on manual intervention (and to remove the human element) what I wanted as an ideal is as follows -

1. Create a candidate record
2. Fill out mandatory fields
3. Save record
4. Have system create the corresponding child record
5. Have an "open" record button on record 1 that then allows it to be opened from within record 1

Does that make sense?
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
Is it necessary to have the Candidate data and the Compliance data on two different tables? And, if so, in what instances would it be necessary to have multiple Compliance records for one Candidate?
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
It sounds like you only want 1 Table 2 record attached to your Table 1 (parent) record.  In this case you simply need the generic [Add Table 2] button created with your relationship to have two functions.

You will need to create the following fields:

1.  Create a Summary Count field which counts the # of Table 2 records ([# of Table 2 Records])

2.  Create a Summary Maximum field which summarizes the [Record ID#] of the Table 2 record ([Max Table 2 Rec ID#])

3.  Modify the standard [Add Table 2 Record] formula so that it will either allow the User to create the Table 2 record when they are populating Table 1.  Make sure you have turned on the checkbox in the Table Advanced Settings to "save parent record automatically when a child record is created"

4.  Your formula will look like this:

If([# of Table 2 Records]=0,

URLRoot() & "db/" & [_DBID_TEST_2] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])& "&z=" & Rurl(),

URLRoot() & "db/" & [_DBID_TEST_2] & "?a=dr&rid=" & URLEncode([Max Table 2 Rec ID#])

)

5.  Your button "label text" should be changed to "Add / View Table 2 Record" so it accurately describes what it will do.

You will need to source your own DBID aliases from your Table's Advanced Settings screen.  The &fidshould be the [Related Table 1] reference field ID# which is the top field on the right side of the relationship window.


If you need any help with this, feel free to reach out to me via my profile.


Another solution to consider is to use the CopyParentChildren feature to create your Candidate records which can auto create the Table 2 record as part of the creation of the Candidate record.  Your users can Grid Edit the Table 2 record, or use the hyperlink Count # field value to access it, or use a custom button to open it up.

I do wonder, also why you need to have your Compliance in a separate table if you only want 1 record there - and why you can't put the data set of values into Table 1 and possibly make them required.

Remember, in a relationship, Lookup fields can be checked so that they "display the parent" and child summary fields will drill down to the child-record data as well (summary fields) - which is very handy as a substitute for the Report Link field which is a default in reports (which I turn off).  The summary field counting the records tells the user if records exist, rather than only link to records (which may or may not exist).
Photo of Hirsty2000

Hirsty2000

  • 30 Points
Apologies for the delay in responding - been ill today.  I'll give this a go and see if it does the job for me.  Thanks so much for your help - I'll let you know how I get on
Photo of Hirsty2000

Hirsty2000

  • 30 Points
Hi Laura.  The first method isn't working as I expected it to as it does not seem to be pulling the information through from the master records.  Can you explain the 2nd method for me in more detail, as I think that is closer to what I'm after?

The reason for the segregation is that whilst part of an overall process, they are to be undertaken by individual teams and the app owner would like it to be segregated.

Thanks
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
Ah, it sounds like you're not properly populating the relationship reference field with the right value.  Please contact me via my profile and I can also explain the alternative option, which might be more favorable for your needs.
Photo of Hirsty2000

Hirsty2000

  • 30 Points
Thanks Laura.  I can't see where to contact you on your profile.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
(626) 771 0454 - click on my name in the first post I responded with and you should be able to find the information there.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
Profile contact information on this page (on the right hand side) https://quickbase-community.intuit.com/users/537-quickbasepros_ids