How do I get a list of fields from found records

Questions
1

I feel like I've asked this before and found the answer before but I just spent another 20m looking for it. Here's the simplified version of what I want.

Goal, craft an email to your team with a list of the first, last, and birthday of everyone named Juan

Here's how I do it now.. EITHER:

  1. do a search for everyone named "Juan"
  2. create a variable to hold results in
  3. Create a script which starts at record one and loops through them all grabbing first name, last name, and date of birth
  4. send that email

This feels like a bit of work so lately I've been bypassing FIlemaker and using ExecuteSQL instead.. although I find that harder to debug and I'm definitely not a SQL pro.

Is there some easier command that basically just says "get this 4 fields from every found record"? I keep wanting ListValues to do this, but it doesn't seem to...

2

Hi @JasonMark

Some thoughts:

  • The scripted approach you mentioned is a reasonable option, but I'd mention that I would not navigate from record to record; instead I would use the GetNthRecord function to allow me to traverse data in the found set. Limitation: GetNthRecord won't help you directly gather data which is related to the found set in the same way (i.e. one or more hops away).

  • The use of the While function, in conjunction with GetNthRecord can work similar to the above. One thing I like about this is that sometimes it's nice to be able to work out the details of something like this, and encapsulate it in a CF that I can use elsewhere, too.

  • Along the lines of CF's, I'd suggest looking for a copy of Agnes' "CustomList" function, which offers a very powerful tool for the types of tasks that you are describing.

  • If it were just one field that you needed to collect from each record, the ListOf summary field could help, but I'd really only reach for that option if I need to use this frequently, and with high performance.

  • I've heard other devs mention that they would consider exporting CSV data of the target fields from the found set, and then use the File reading functions to read the data back into memory from within a script. I have never done this, and though I don't rule it out as an option, I don't suspect I'll be doing it soon, but I do have to say that I appreciate the ingenuity of it. I imagine it could have a useful place of the found set is huge.

  • I also once mocked up a technique for scraping data from a found set into a global field by using the Replace command. I don't advocate that one, however, because the Replace command can show a dialog that can be confusing to the user.

I guess that's a few ideas for now. Circling back, I'll reiterate that I think your scripted approach is a fine way to go, but again emphasizing that my preference is to use GetNthRecord instead of navigating the found set.

HTH.

2 Likes
3

The Send Email script step works with the current found set. Check the options in that script step to see if it meets your needs.

1 Like
4

In this case I would definitely go for SQL! It only takes you a a single line of code to gather that data. And since your search criteria are very simple this shouldn't be hard to be debugged.

Once you dig into SQL you will find it more and more easy to do. I am also not a hardcore user of SQL but often times there is just no easier way to gather data. It helps me a lot, when I format my SQL statement like this:

ExecuteSQL ("
SELECT :arrow_right: field
FROM :arrow_right: table
WHERE :arrow_right: field LIKE '%criteria%'
"; "" ; "" )

:arrow_right: There are tabs between the command and the definition, that the forum doesn't show, in order to make the statement more human readable.

This site is a great place to learn:
https://www.w3schools.com/sql/

I hope that helps. Sorry for your time, if you already knew all of the above mentioned.

2 Likes
5

The challenge is in this case I use Primary Keys to join things so it needs to be:

Get date and notes from this field, and then get the staff member who made the note from related staff table, and then get the student name from the related student table.

Which gets more than a little more complicated in SQL.

What I might do is just add a Calc field in Filemaker. I feel like that will bloat things a bit, but then I could do an easy SQL call like you describe...