Quick Base Discussions

Expand all | Collapse all

Enforce single use of a record in a relationship

  • 1.  Enforce single use of a record in a relationship

    Posted 01-29-2019 14:59
    I am having a bad brain lapse.

    The basic use case is, I have Sample Data that we combine with 4 other pieces of Sample Data to produce a Unique outcome. Each piece of Sample Data can only be used Once.
    In case it is confusing the best way to look at it is each sample is a limited resource with only enough of that resource to be used in one test combination.

    I am running myself in circles on how to set up the structure to enforce each Sample can only be used once.

    I was looking at doing 3 tables, SampleData, UsedSamples & CombinationResults.
    With UsedSamples being the join table between SampleData and CombinationResults.


    Was looking at Key field in SampleData is an 8 digit unique text field and a unique 8 digit Text field as the Key in the CombinationResults Field.

    Any simple or brilliant ways for me to enforce the single use of each SampleData?










  • 2.  RE: Enforce single use of a record in a relationship

    Posted 01-29-2019 15:52
    np,
    A Parent can have many children, but a child may only have 1 parent.

    ie a Sample if the sample is setup as a Child in a relationship, it may only be attached to one parent as a child. 

    So the only Samples that are available to use are where there is no parent.  I like to set up a field on the Parent called [Parent Exists?] as a formula checkbox field with a formula of true and look that up down to the Child.  Just in case the parent gets deleted, and yet [Related Parent] sill has value in it but the child is now once again an Orphan.


  • 3.  RE: Enforce single use of a record in a relationship

    Posted 01-29-2019 16:08
    Thank you for the response!

    That makes sense but where I am struggling with that is how do have my list of Samples without having a Parent to relate them to? Doesn't it require a Parent to create a child record?



  • 4.  RE: Enforce single use of a record in a relationship

    Posted 01-29-2019 17:06
    It does not require a parent existing to create a child.
    Consider the invoicing and time tracking system for a non so theoretical QSP (QuickBase Solution Provider) like myself.

    I log my time (Add time records) and these are time records that will later need to be attached to a parent invoice.  Then at the end of the month I push a button to mass update them to attach them to the correct parent invoice that will not be created until I sit down to do my monthly invoicing.  So the children get created all during the month and the parent only gets created at the end of the month.



  • 5.  RE: Enforce single use of a record in a relationship

    Posted 01-29-2019 20:38
    I think Yearend has fried my brain, so apologize for the constant clarifications.
    Of course, you are correct the child is not required to have a parent.
    I have played around with a couple of different ways.

    If I do a Two Table setup I don't see how to select existing data into the parent. 
    • Is it just manually creating a blank parent and then going to a report on the child table that filters out any with an existing parent and adding the new parent ID?
    • Is there a way to do that directly from the parent?  
    Using 3 tables it appears to work by using a filtered report in the join to filter out any that have existing parents just cannot do it from grid edit.

    Thank you again for your help!



  • 6.  RE: Enforce single use of a record in a relationship

    Posted 01-30-2019 03:17
    I think I understand.

    You just need two tables and one relationship. One Combination has Many Samples. What is confusing you is that the samples already exist so it�s a matter of selecting Samples to associate to the combination and not creating children like most Apps do.

    There are two solutions here.

    You can either look at a report of Samples which are not allocated to. Ombinations and edit then one by one to select a combination. The drop down list on the sample form can be limited to Combinations which have fewer than 4 Sample children. You do that by making a report filtered that way and setting the form to use that report as the drop down list.

    But I can see that what you really want to do it to have a way to launch off the Parent Combination Record and select up to 4 unassigned Samples.

    I can help you set that up but it requires the creation of some URL formula fields that are beyond the scope of what I can explain in this forum.

    If you would like some help in setting this up, contact me via the invitation in my website.

    QuickBaseCoach.com


  • 7.  RE: Enforce single use of a record in a relationship

    Posted 01-30-2019 03:40
    Thank you.
    I posted some clarification and new information given to me tonight. It is outside of my original question.
    I need to relook at what we were going to put together for it and see if I can come up with something that will work but if not I will see if they will give me an allowance for outside assistance and reach out to you via your website.

    Thank you again for taking the time to look at this.


  • 8.  RE: Enforce single use of a record in a relationship

    Posted 01-29-2019 21:02
    I am going to go I think simpler on this but let's see if I get the basics.
    1 main sample to be combined with another of 4 samples. The 4 samples are limited so they can only be used once and I assume the main sample is a quantity of 4 since you want to combine it with 4 other samples (separately of course).

    I would make a text formula to combine the sample names [Main Sample]&[Limited Sample] then mark the field as 'must be unique' which will prevent the combination to be duplicated. (give a real mean warning...okay not that mean).
    Then put the limited sample in a table and the main samples in another table. Even if I am wrong about the main sample you can use this method to prevent combinations.


  • 9.  RE: Enforce single use of a record in a relationship

    Posted 01-30-2019 03:33
    Thank you both!

    It is actually combining 5 different samples to create a new single sample.
    Each of the 5 samples has specific characteristics(data) and we are wanting to combine the 5 to create a unique new sample with a new set of characteristics. Some will be measured and some will be through calculations based on data associated with each of the 5 samples used.

    Now some new information as I discussed the suggestions with the project manager this evening was that they want us to be able to use the newly created samples in future combinations as one of the 5 samples used to create a new combination. Same rules apply that once a unique sample is used in a combination it can no longer be used in any future combinations. All samples, including those created through the combination, have a unique 8 digit text serial #.

    This is getting outside the scope of what I was asking in the initial question and I need to re-evaluate the app.

    If you have any further thoughts I am always grateful for any advice/help.
    Either way, Thank you both for your time and thoughts!


  • 10.  RE: Enforce single use of a record in a relationship

    Posted 01-30-2019 12:37
    No problem. I like puzzles. Once you are sure what you want there is definitely a way to improve the workflow using a some native, (no script) but somewhat advanced techniques with URL formula buttons.


  • 11.  RE: Enforce single use of a record in a relationship

    Posted 01-30-2019 21:34
    You mentioned you need to re-evaluate but I wanted to mention the possibility of a "slotting" system. If you can figure out a way to categorize the sample data into 4 or 5 unique parent tables, then each slot will contain data unique from the others. For example, Slot 1 contains samples 1 through 10, Slot 2 contains samples 11-20, etc.. 

    You can take this further by creating form rules that display messages if slot1 =slot2 (parent 1 = parent 2) etc. Or you can create a formula field that generates a unique number that is required in order to save the record, but the number is only generated when no slots are matching.