8 Replies Latest reply on Oct 24, 2013 6:25 AM by memanic

    lookups on repeating fields


      Help! I am a newbie to FM although I usedit many years ago. I have a problem I need help with:


      I work with a non-profit food pantry. I am trying to design in FM 10 Pro a data entry form for donations that will lookup the value of each item, copy it into the value field and then calculate the total value. For instance, I receive 3 cans of peas. I want to be able to type in "peas, canned" in the item description and "3" in the # field. I want it to lookup the value, multiply x # and put the result in an "EXT" I have already created a separate table with "food name" and "food value." I can get it to work for a single entry, but I want a total list of everything from that person so that I can total it. I have tried repeating fields, no luck; also tried portals It seems so straightforward, but I can't figure it out. Also, we accept clothing which entails the same problem. Please help someone!!!

        • 1. Re: lookups on repeating fields
          Stephen Huston

          I took your file and added a realted table for donation line items, then created a portal area on the donation form for entering and viewing them. Zipped new file attached.


          I hope this helps you get started with related portal items, which are far superior to repeating fields for almost everything.


          You will need to add some fields to the Donation_Items table, and set the lookups there.


          I hope this helps you get started.

          1 of 1 people found this helpful
          • 2. Re: lookups on repeating fields

            Thank you so much for your time and knowledge. You gave me a great answer and even set up the portal for me!  I was hoping, though,  that I could do everything on one form and eliminate the need for a huge file of single donations. We sometimes can receive as many as 5,000 items per month, not including cash. Is that even possible in any commercially available program? I have looked at Access and Infopath, but they don't seem to be viable options either. Again, thank you so much for your efforts on behalf of The Clearinghouse for Emergency Aid.  Pat R.

            • 3. Re: lookups on repeating fields

              Have you looked at Little Green Light?


              Please note, I have no experience with the product, but I have non-profit clients who consider it a good fit for them.

              • 4. Re: lookups on repeating fields
                Stephen Huston

                Keep in mind that the data itself is what causes a file to increase in size -- data structure, once in place, isn't a source of future file-size growth. This means that adding data to a related table is not going to make your file grow significantly than adding data to repeating fields.


                The portal/related data is simply a cleaner structure, and the related line items can be used at least as well as repeating fields, but without the limitations inherent in repeating field data. Since the tables reside in the same file, there is no downside to using the related table for line-item data of which I am aware.

                • 5. Re: lookups on repeating fields

                  Hi Pat R,


                  If you are using FileMaker yet (10, 11 or 12), there two great Apps for non profit orgs developed with FileMaker and both are free (CC Non Profit also offers a paid Pro version).


                  1. Richard Carlton of RCC Consulting offers RCC Donantions with a  easy  customization options.
                  2. Cleveland Consulting offers CC Non Profit with a nice design and specs.


                  I hope this options will help you to work in your Non-Profit projects with the power and flexibilty of FileMaker ;-)


                  Best regards from Spain.



                  Eduardo Croissier

                  • 6. Re: lookups on repeating fields

                    Thanks so much for your helpful comments. I appreciate your expertise. With your help I was able to create a portal and populate it with individual entries. Now I have another question: I have set up a report to find all single donations by date (or range), sorted them by donor # and totaled the extended donation value in a subsummary for each donor. I would like to extract a given donors' subsummaries and sort them by date, then show them in a separate portal.  However, the "total this donation" field shows a grand total all the way down the list--the same number, that is. The dates vary and the items vary, but not the total. Do I need to define a different field to handle the subsummaries?

                    • 7. Re: lookups on repeating fields
                      Stephen Huston

                      You may need a self-join relationship for the line-item donations table in which the date is included in the relationship, along with the parentID, then do your subtotal as a calculation across that relationship. Then it would show a different total amount for records with different parentID and different dates of donation.

                      • 8. Re: lookups on repeating fields

                        Thanks! I’m going to try this and see. I suspected that it might take a self-join. Maybe I’m learning? Thanks again.