6 Replies Latest reply on Dec 19, 2012 12:55 PM by aklobby

    Extract Data from Piggybacked Field



      Extract Data from Piggybacked Field


           Filemaker beginner here and silly me tried to do an advanced trick without full knowledge of what I was doing. Using a data separation model and an oblgatory create, I created a field to hold all phone numbers and a field to hold all emails, this lets me enter multiple phone numbers and label them home, work etc.... I am then able to view this data in a web viewer and it is really cool.

           The problem I am having is now I need to use a phone number and an email as a primary number or email address and do not know how to do this. Can I somehow mark one of the numbers with a check box or button and have that copy into another field? If so, what calculation would work to accomplish this?

           Any advice (like, don't quit your day job) or any assistance is greatly appreciated. 


        • 1. Re: Extract Data from Piggybacked Field

               Is each phone number in a separate record of a related table?

               Do you use a portal to display them?

               If so, do you have a relationship similar to this?


               Contacts::__pkContactID = PhoneNumbers::_fkContactID

               (See first post of: Common Forum Relationship and Field Notations Explained if this notation is not familiar.)

               If so, you can add a field to contacts:: PrimaryPhone and use it to store the contents of __pkPhoneID--the primary key field that defines a specific phone number record in PhoneNumbers. Then you can add this relationship:

               In Manage | Database | relationships, make a new table occurrence of PhoneNumbers by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be PrimaryPhoneNumber.

               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

               Add it to your relationships like this:

               Contacts::PrimaryPhoneID = PrimaryPhoneNumber::__pkPhoneID

               Now you can add a button to your portal of phone numbers to click to select that phone number as the primary phone number:

               Set FIeld [Contacts::PrimaryPhoneID ; PhoneNumbers::__pkPhoneID ]

               With all of this in place, if you add fields from PrimaryPhoneNumber to your layout, they will display data from the selected PhoneNumbers record when you click the button for that phone number in the portal.

               Additional Notes:

               When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

               With conditional formatting, you can set up your button in the portal to look and function like a check box field where clicking the button both enters the ID number into the correct field as shown above and also makes the letter "X" or a "tick mark" character appear or disappear to show which number was selected as the primary number.

          • 2. Re: Extract Data from Piggybacked Field

                 Phil, thank you for your quick response. 

                 Each phone number is in one field inside an attributes table. This table includes addresses, phone numbers and emails. They are identified in the table as a type number (phones being type 2, emails type 3, etc...) And are further identified by labels (work, home, other, etc...). So in the attributes table there is a type, label and value field that correspond to the thype(phone), label (work) and value (phone numer). This attributes table can be related to the contacts table. I have tried making a field in the contacts table that would provide a calculated result from the attributes table but I could not get a calcuation to work.


            • 3. Re: Extract Data from Piggybacked Field

                   I wouldn't do it that way to begin with--contrary to what you find in the starter solutions.

                   I'd use a related table and a portal. This allows you to specify a fully flexible amount of contact phone numbers for each person.

                   Using the "one field for each phone number in the same record" design, you could set up a number field for each phone number field. Format it as a checkbox field with "1" as the only value in its value list. Put these fields next to each phone number field so that you can click one to select it.

                   Then a calculation field similar to this could display the primary contact number:

                   Case ( CheckboxField1 ; PhoneNumberField1 ;
                              CheckboxField2 ; PhoneNumberField2 ;
                              CheckboxField3 ; PhoneNumberField3 )

                   Include one such pair for each phone number field in your record--using your field names in place of mine.

                   The one drawback that you encounter is that clicking a checkbox next to one phone number will not automatically clear any of the other checkboxes and your calculation will show the phone number of the first such field to have a 1 in it's corresponding check box field.

              • 4. Re: Extract Data from Piggybacked Field

                     Good to know for future data bases. I just managed to create the primary number by writing a copy and paste script that I can run from the portal that will paste the desired value in a primary phone field. A little clumsy, but works until I can delve into your suggestions. 

                     Thank you again for your help. 


                • 5. Re: Extract Data from Piggybacked Field

                       I strongly recommend that you not use that script:

                       1) any script that copies data to the clipboard destroys any data the user may have copied to the clipboard prior to performing this script. The sudden unexplained change in the data in the user's clipboard is annoying, confusing and easily avoided in this case. Use set field to transfer the data instead of copy/paste.

                       2) with the phone number copied to this field via script, you now have one phone number stored in two locations. Say you select a person's home phone number as the primary number and then you are notified that their home phone number has been changed. Because this number is entered into two different fields, you have to identically update both fields to implement this update. If you change the data in one field and not the other, you have introduced an error into your data.

                       Both methods that i've described are designed to avoid this potential discrepancy in your phone numbers.

                  • 6. Re: Extract Data from Piggybacked Field

                         Thanks for the advice. I will work on that.