1 2 Previous Next 18 Replies Latest reply on Jan 16, 2013 1:33 PM by EP

    Setting fields as primary/secondary?

    EP

      Title

      Setting fields as primary/secondary?

      Post

           I have a contacts table and an address table.  Some cntacts have 3 or more addresses.  I am going to display each address within the Contact layout via a portal.  When Invoices are genrated (invoices table), I want the primary and secondary address to show up on the invoice.  Anyone care to share techniques on how they mark addresses as primary and secondary for displaying on, let's say an invoice, without having to manually select each address to add to the invoice each time?

           P.S. I'm using addresses as an example, but I have other fields to apply this to, so I just need to learn the general technique to apply it across my solution.

           Thanks

        • 1. Re: Setting fields as primary/secondary?
          philmodjunk

               Those "primary" and "secondary" addresses might be the billing and shipping address. There are methods that you can use and both are applicable in different situations.

               With "shipping" and "billing", the same address could be both. The easiest way to manage that is to have two fields in Contacts::

               BillingAddressID and ShippingAddressID. Each would be used as match fields to link to different occurrences of the address table.

               In other cases where you have a "secondary" and a "primary", you might want these designations to be exclusive. In which case, you can add a field in the addresses table that designates a record as "primary" or "secondary". Then a portal filter expression can specify one value or the other to select for that specific address and a calculation field defined in contacts can be used as a match field if you want to get this same result at the data level (Which is often needed for calculations to evaluate correctly.)

               Contacts::ContactID = PrimaryAddresses::ContactID AND
               Contacts::constPrimary = PrimaryAddresses:::ContactType

               Other options:

               A checkbox formatted field in Addresses can be used to designate records as primary, secondary or both and this field could be used in the above relationship to get the same result as having two ID fields in Contacts.

               If you have FileMaker 12, consider using Execute SQL to pull up data from such a related table where the "type" can be specified as a "Where" clause.

          • 2. Re: Setting fields as primary/secondary?
            EP

                 I went the checkbox route. Acutally might make it a radio button.  I have Contacts::_PKClientID=Addresses::_fkClientID.  I'm stuck here, as I'm not sure what the second relationship you posted would translate to in my solution (Contacts::constPrimary = PrimaryAddresses:::ContactType).  What fields would I use for this?  I'm also confused about how to designate one address as primary without the other addresses having the option to be primary; what is to prevent a user from choosing 3 addresses as primary since the checkbox set will be available in each address record?

            Thanks

            • 3. Re: Setting fields as primary/secondary?
              EP

                   I ended up creating 2 fields called primary and secondary, respectively.  I set them on my layout with a popup value list, listing the address records related to the contact.  The user can select primary and secondary.  Not sure if this is "best practice" but works in the meantime.

              • 4. Re: Setting fields as primary/secondary?
                philmodjunk
                     

                          I have Contacts::_PKClientID=Addresses::_fkClientID.  I'm stuck here, as I'm not sure what the second relationship you posted would translate to in my solution (Contacts::constPrimary = PrimaryAddresses:::ContactType).

                It depends on whether you want to use two relationships, one for primary and one for secondary or just one relationship with a field used to select the desired address record.

                If you want to select the address record on a case by case basis, use:

                     Contacts::ContactID = SelectedAddresses::ContactID AND
                     Contacts::SelectType = SelectedAddresses:::ContactType

                     This is one relationship with two pairs of match fields. ContactType would be a checkbox formatted (or radio button formatted) field and You'd set SelectType up with the same value list for selecting either the primary or secondary address record.

                     If you want to have two relationships, "hardwired" to match to just the primary or just the secondary address:

                     Contacts::ContactID = PrimaryAddresses::ContactID AND
                     Contacts::constPrimaryType = PrimaryAddresses:::ContactType

                     Contacts::ContactID = SecondaryAddresses::ContactID AND
                     Contacts::constSecondaryType = SecondaryAddresses:::ContactType

                     Assuming that your value list consists of two values: Primary and Secondary...

                     constPrimaryType would be a calculation field, set to return text with this expression: "Primary".
                     constSecondaryType would abe a calculation field, set to return text with this expression: "Secondary"

                • 5. Re: Setting fields as primary/secondary?
                  EP

                       I'm not using addresses, rather insurances for a patient.  This is a screenshot of my layout.  This layout uses the Patients table and the portal you see is from the PatientInsurance Table.  What I am struggling to grasp is: How will I list all insurances belonging to a patient in the portal, while allowing users to select which is primary and secondary?  What is to stop a user from selecting 3 insurances as primary?The 3 fields below my portal is my current setup where the user has to choose the primary and secondary via a popup using a value list.

                  • 6. Re: Setting fields as primary/secondary?
                    philmodjunk

                         This would be why you would select the Id code for primary insurance in a field in the Patient table rather than the insurance table.

                         Patients::_fkPrimaryInsuranceID = PrimaryInsurance::__pkInsuranceID

                         Patients::_fkSecondaryInsuranceID = SecondaryInsurance::__pkInsuranceID

                         These are in addition to:

                         Patients::__pkPatientID = Insurance::_fkPatientID

                         _fkPrimaryInsuranceID and _fkSecondaryInsuranceID can be formatted with drop down lists of a Custom Value List? listing all insurance records for that one patient. And a field validation rule can be set up to prevent a user from selecting the same Insurance Record as both Primary and Secondary, if that is a concern. (Or if you really want to get fancy, the conditional value list can be so set up as to exclude an insurance record selected as the Primary from the drop down for the secondary and vice versa.)

                         Note that PrimaryInsurance and SecondaryInsurance would be two "occurrences" of the same Insurance data source table. If "Table Occurrence" is a new term: Tutorial: What are Table Occurrences?

                    • 7. Re: Setting fields as primary/secondary?
                      EP

                           Safe to assume that Patients::_fkPrimaryInsuranceID and  PrimaryInsurance::__pkInsuranceID are text fields?  (The insurance ID's have numbers/letters in them).  As I'm setting this up, it seems that this is how I have it set up in the pic I posted, except I didn't have the Patients::_fkPrimaryInsuranceID = PrimaryInsurance::__pkInsuranceID relationship in place.

                      • 8. Re: Setting fields as primary/secondary?
                        philmodjunk

                             They would typically be number fields. __pkInsuranceID would be an auto-entered serial number field in most FileMaker databasea, though text fields with an auto-entered calculation with Get ( UUID ) is also possible in FileMaker 12.

                        • 9. Re: Setting fields as primary/secondary?
                          EP

                               I changed the _pkInsuranceID and _fkPrimaryInsuranceID to number fields as you suggested.  I created a value list to list only the records related to the patient for use with _fkPrimaryInsuranceID, but since this is a number field, I cannot display the insurance name and letters that are part of the policy ID number.  My value list consists of 2 fields: insurance name and policy ID number.  

                          • 10. Re: Setting fields as primary/secondary?
                            philmodjunk

                                 It will if you use a pop up menu and specify that only the second field be visible.

                                 You can also place the Name field from the related table next to or even on top of your drop down list formatted ID field. If you place the field on top, you can give it an opaque fill color to hide the field behind it and use Behavoir settings on the inspector's data tab to deny access to the name field while in browse mode. Then, when you click on this field, the drop down list behind it pops to the front and displays the list of values. When you select a value, the drop down list disappears back behind the name field and the name field then displays the name from the related insurance record.

                            • 11. Re: Setting fields as primary/secondary?
                              EP

                                   I don't have that option selected (only show values from second field).  It still only displays the numbers, weird.  

                              • 12. Re: Setting fields as primary/secondary?
                                philmodjunk

                                     Let me repeat:

                                     If you use a POP UP MENU, and select "Show only values from second field", the second field value will appear in the field when you exit, even though it is storing the ID number.

                                     If you use a DROP DOWN LIST, the second field value will not appear with any of the possible value list options--but my previous post spelled out a way you can still get that name to appear.

                                     You posted:

                                     

                                          I don't have that option selected (only show values from second field).

                                     Therefore, the value of the second field will not be visible with either value list option unless you add that field from the Insurance table to your layout.

                                • 13. Re: Setting fields as primary/secondary?
                                  EP
                                  /files/5135026a3b/Screenshot_1%3A15%3A13_8%3A38_PM.jpeg 567x158
                                  • 14. Re: Setting fields as primary/secondary?
                                    EP

                                         Ive included 2 snapshots.  1 illustrates the relationships I have set up and the other shows my layout using the patients table with a portal from OfficePatientsInsurance and the _pkInsurancePolicyID is from the PrimaryInsurance Table occurrence of the OfficePatientsInsurance table.

                                         Phil my apologies if I am causing you to lose patience on this topic.

                                         Can someone review my relationships and let me know if everything is in place?  What alarms me is that the _pkInsurancePolicyID is from the PrimaryInsurance Table occurrence is showing "blank" and uneditable on my layout, causing me to think I didn't create the relationship properly. Eventually I am going to create a script that exports Patient information, including primary and secondary insurance to an excel file.  A patient may have 5 insurances in their related records over time and I want users to be able to simply click a button to export the proper information (as long as they selected the primary and secondary correctly to begin with).  I want to make sure I have everything in place in preparation.

                                         Thanks

                                    1 2 Previous Next