Discussions

 View Only
  • 1.  Limit child record status with a checkbox

    Posted 07-14-2020 16:08
    I currently have two tables in my app, 'Vendors' (parent) and 'Vendor Contacts' (child) - one Vendor to many Vendor Contacts.

    In my Vendor Contacts table, I added a checkbox named 'Primary Contact'. I'm trying to limit Vendors to allow only one Vendor Contact with the 'Primary Contact' status. I created a reverse relationship as well, where Vendor Contacts is the parent to Vendors, with Primary Contact as a lookup field in the Vendors table.

    At this point, I'm not certain what to do to next to make sure that Vendors only has one Primary Contact checked off where I can hide the checkbox if Primary Contact is already selected in any of the Vendor Contacts related to one Vendor.
    Vendors to Vendor Contacts
    Vendor Contacts to Vendors


    ------------------------------
    AR
    ------------------------------


  • 2.  RE: Limit child record status with a checkbox

    Posted 07-21-2020 13:02
    You could use a data validation rule (advanced table settings) in your vendor contacts table. 

    1. Summarize # of primary contacts up to Vendors (you probably already have this)
    2. Create a lookup of # of primary contacts down to Vendor Contacts
    3. Create a data validation rule in Vendor Contacts, something like If  [# of primary contacts]>0​​ (or not null depending on your field settings), "You already have a primary contact...."*

    If you do have the reverse relationship you could also lookup that persons info back to vendor contacts as well and incorporate that into your data validation message, i.e. "...his name is xxxxxx"

    hope that helps!

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



  • 3.  RE: Limit child record status with a checkbox

    Posted 07-21-2020 15:52
    Hi Charlie,

    I tried summarizing the number of primary contacts up to Vendors, but I got an error message: "That summary function cannot be applied to this field".

    Thanks,
    Angel

    ------------------------------
    AR
    ------------------------------



  • 4.  RE: Limit child record status with a checkbox

    Posted 07-22-2020 10:33
    You'd want to configure your summary as: "number of Contacts related to that vendor" and filter where primary contact = checked

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



  • 5.  RE: Limit child record status with a checkbox

    Posted 07-22-2020 11:13
    Edited by Mark Shnier (Your Quickbase Coach) 07-22-2020 11:18
    Charlie
    I think that there is a simpler approach.
    You already have a relationships which is the reverse relationship where 1 Vendor Contact has many Vendors. Obviously that is the Primary contact and conveniently the lookup info for the Primary contact will be available right on the Vendor record.  Also by the fact that the Vendor may only have 1 "Parent" as a Primary contact, you can only select one.  There is not a way to select more than one.

    If you want to flag the child table or colour it to indicate which is the Primary, just make a field summary field on the reverse relationship to count the # of Vendors.  Where that is not zero, then that is the Primary Contact.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------