9 Replies Latest reply on Mar 9, 2016 9:59 AM by srbridges

    Need advice on how to perform data entry two TOs away...


      Screen Shot 2016-01-30 at 1.41.38 PM.png

      I have a "home" layout based on the Contacts table. I would like to be able to create records in the Affiliation table, and then create records in the Donations table which will be related to the Affiliation table - all from this "home" layout.

      Is there a way to do this using portals, popover windows, slide controls? Right now I can create the Affiliation record in a portal, but I can't put a portal in the portal, so how can I create the Donation record?

      Any advice would be greatly appreciated..

        • 2. Re: Need advice on how to perform data entry two TOs away...

          Thank you so much for your reply, but I don't see how your suggestion would make the donation record related to the affiliation record, which I need for communications purposes.... This solution is for a non profit. Contacts can be members or donors (or both), on an annual basis. Affiliations are annual. and have categories like member, sponsor, etc.  A member can pay her own dues, or be sponsored by another contact. Donations are linked both to donors and members (which most times are the same contact, but not always). In order to send tax receipts, membership acknowledgments, etc. I need each donation to be tied to the affiliation record of the donor. I am accomplishing this now, but it requires going to different layouts based on different tables. To streamline data entry, I would like to be able to do this from one layout. I have thought about having a button in the affiliation portal that would go to a donation layout and create a new record, but I am not sure if that would be the most efficient. And would I have to copy affiliation ID, contact ID from affiliation record and paste them in donation record?? Working in a portal would be so much simpler, but I can't figure out how to do it.

          If you have further explanations, that would be great.

          • 3. Re: Need advice on how to perform data entry two TOs away...

            The point of my demo file was not to provide you with a complete solution but to provide a suggestion on a possible way to create related records where there is a join table involved.

            You could easily adapt the technique to your specific problem.

            • 4. Re: Need advice on how to perform data entry two TOs away...

              perhaps use the pivot method ( look it up)


              something like this should be viable


              create a global ( e.g. in Contact) g_pivot_id

              create a  REL between Contact::g_pivot_id and Affiliation::affiliation_id  ( i.e. the PK)

              set the rel for acrr


              create a global ( e.g. in Affiliation) g_pivot_id

              create a  REL between Affiliation::g_pivot_id and Donations::donation_id  ( i.e. the PK)

              set the rel for acrr



              clear the (contact) pivot global

              set affiliation contact_id = Contact contact_id

              set other fields as required

              clear the g_pivot id and commit




              clear the Donation pivot global

              set Donation affliiation_id = donation_id

              set other fields as required

              clear the g_pivot id and commit




              or create  a utility table related to the contact table ( constant predicated for example 1=1), and put the pivot global in that and path use 2 acrr REls,    Cleaner to have globals in a Utitity table. Plus with the globals cleared at end of scripting, no related load to calculate/resolve...


              Pivot is a very tidy method once one gets to know it.

              • 5. Re: Need advice on how to perform data entry two TOs away...

                Thank you again for your reply. However, I don't think I have the skills to understand/implement what you are suggesting but I certainly appreciate your effort!

                • 6. Re: Need advice on how to perform data entry two TOs away...

                  What does acrr stand for? I did look up Pivot tables, but I don't understand how I could use that to solve my data entry problem - Thank you for your reply, but even though you gave very helpful detailed steps, I don't understand how to implement it... I should have said upfront that my Filemaker skills are limited....


                  I think that I will put an Affiliation table portal on the contact layout and have a button in the portal row that will open a new window from the Affiliation table showing same affiliation record where I will place a portal to the Donation table.

                  • 7. Re: Need advice on how to perform data entry two TOs away...

                    acrr - allow creation of related records; it is a setting on the child side of the relationship definition

                    I would suggest you have a record creation 'problem' primarily, data entry is secondary.


                    You could create your records manually as you indicate, and the RELs would be defined acrr, allowing you to manually create records in the child (portal) table by entering a value in one of the child (related) fields in the portal last row.


                    Or you could use scripting  to achieve the same result, with essentially one click. ( how many related records to create is another issue).


                    In order to create multiple related records, when entering a value in a portal that uses a acrr REL, you enter a value in the last blank row. This will work even when the relationship is a single predicate e.g.  Affliiation::affiliation_id = Donations::affiliation_id


                    When you try to script an equivalent, the REL values need to be unique for each new record. If it were just Affliiation::affiliation_id = Donations::affiliation_id, then a script would just ovewrite the same related record. So multiple predicates are required, and a unique timestamp is one way of approaching this, as is adding multiple predicates to the REL definition.


                    Another way would be with a script  to parse the Affiliate_id to a variable $_affiliation_id, then

                    go to layout Donation

                    create new record

                    set Donations::affiliation_id = $_affiliation_id

                    create new record

                    set Donations::affiliation_id = $_affiliation_id


                    and return to Affiliate


                    A simpler and more elegant (and faster)t way to create a related record is to use the pivot method.

                    If you want to pursue this we can work through it.


                    Learning to script create related records  is essentially unavoidable, if you intend to use FileMaker in any but the most basic ways.


                    Attached is a simple demo file that uses the pivot method.

                    As I indicated in earlier reply, the pivot global could be in each of the contact and Affiliation tables, with appropriate RELs, but cleaner to to have a dedicated utility table for globals.


                    Additional advantage of pivot over go to layout-create-return, is that it is faster, and also that it avoids screen flicker on windows.

                    Advantage of pivot method over multi-predicate rels is obvious, one predicate rather than multiple.

                    • 8. Re: Need advice on how to perform data entry two TOs away...

                      I think the best way to go is to...

                      Have a portal of Affiliations. Users can edit the Affiliation data there. A user will click a button on a portal row and the related Donations for that Affiliations will show in another portal. To create a new Donation, users will click a button to create the Donation record, though data entry will be done in the portal.

                      To do this:

                      Delete the relationship between Affiliations and Contacts.

                      Add a relationship between Contacts and Donations using Contact/Donor ID. Do not allow create related records.


                      On your Contacts layout, create a portal showing Affiliations. Create your Affiliations through that.

                      Write a script that will set a variable ($$affiliation.choice) equal to the Affiliations pk.

                      Attach that script to a button on the portal.

                      On your Contacts layout, create another portal showing Donations. Filter that portal to show records when fk_Affiliations = $$affiliation.choice

                      Write a script that creates a new Donation record and sets the fk_Affiliation field to $$affiliation.choice.

                      • 9. Re: Need advice on how to perform data entry two TOs away...

                        If I delete relationship between Contacts and Affiliations, how could I have Affiliations portal on Contact layout? I basically follow what you are suggesting, and think it will work, but not sure why you want me to delete the contacts-affiliations relationship..