5 Replies Latest reply on Jun 23, 2016 12:48 PM by okramis

    If Statement Between Two Repeating Lists


      Some Background/Introduction...


      Hello! I am an exceptionally novice level FM user. I started my current position a few months ago and went from a company where I was the Excel Guru to a company that exclusively uses FileMaker Pro. Eep. So now that I'm comfortable with my work I've started poking around in the program and have a desire to make the following happen but am a loss within my limited resources... (I often come by this site to peruse for solutions, so I thought why not poke my head in for real!)


      Skip to the Question...


      I have two repeating lists... one is a list of Individuals (Names, Emails, etc). The second is strictly a list of e-mails. These two are not related within the database other than literally being on the same layout. The second list is literally just so I can send invoices to a set list of people at the customer's company, some of which are not listed in the Individuals list because they are generic emails (like ap@company.com).


      What I've been scratching my head about is being able to look at the first e-mail on the Invoice Recipients list, see if they are anywhere on the Individuals list, and if they are grab their Name and plop that into the body of the e-mail. It was easy enough to get this to work when the recipient and the Individual were the only names (or the first names) on both lists, obviously. That was just an IF statement. But grabbing a name that might be buried 8 records down the Individuals list... that's got me stumped.


      I literally have nothing right now other than the simple IF statement that can grab what I need if it's a 1-to-1 search...


      IF ( Individuals::Email = Recipient::Email ; Individuals::Name ; "X" )


      Thank You in advance!

        • 1. Re: If Statement Between Two Repeating Lists

          can you describe what you want to achieve ? Extra points for coming up with a definition of a "repeating list".

          • 2. Re: If Statement Between Two Repeating Lists

            I want to be able to look at the email in Recipient field (repeating field, not list , I think that's the official name? Like I said, I'm brand new to all of this and some of the terminology hasn't quite made it into my brain) , search for it in the Individuals::Email field, and then dump the Individuals::Name into the body of an e-mail, as stated in the question. I'm using this for sending out e-mails to a set list of people, the e-mail being addressed to whomever is listed first in my Recipients list.

            • 3. Re: If Statement Between Two Repeating Lists

              The FilterValues (listA ; listB) function is probably what you're after.  But it only operates on actual lists (a text field of return-separated values) and not on repeating fields.  You could also use the Position ( textToSearch ; searchString ; start ; occurrence ) function to search for a specific value searchString within textToSearch.


              We need to know a little more about the structure of your database as well as how (and maybe why) you're using the repeating fields.

              • 4. Re: If Statement Between Two Repeating Lists

                That Position function is really interesting, and will come in handy later down the road! So thank you for that tidbit there.


                As far as the database structure and why repeating fields were chosen, I really can't answer that because this was all in place well before my presence here (except the  Recipients field.) I have a suspicion however that it has to do with the fact that we may have one contact for a company, or we may have 20, so they wanted the list to be of variable length/flexibility. I will have to ask the creator of this DB for his original intentions with that design choice.


                All I can tell from here is that the Individuals::Email field is it's own table on this layout, whereas the Recipients field is ... just a field. That one I could easily transform into a text-only field, I believe, since I'm the one who implemented it with guidance from a supervisor who suggested the repeating field. (I'm adding to this existing database to make my job a little easier here and there, thus creating situations where I can learn about how this program works.)

                • 5. Re: If Statement Between Two Repeating Lists

                  The best thing would probably be to redesign your db and get rid of those repeating fields. If this is not possible, here's a great blog from Will M. Baker at beezwax with a collection of custom functions to work on repeating fields:


                  Working with Repeating Fields | beezwax > blog


                  best might be to pull the values of the repeating field into a list and use ValueCount ( FilterValues ( theList ; theSearchString ) )


                  best regards