Exporting all portal entries

Working on a "membership" database.  I want to email all the "chiefs" in charge of each "tribe" all of the demographic information for each member in the tribe.

Originally, I started with a single membership database (all chiefs are members too).  To make it easier to think about relationships, I created a second copy of the database that had chiefs in it only.  I can relate these two file via the "tribe name".

I'd like to have a field in the chiefs database that contains concatenated information for all the members' info for the tribe that the chief is in.  I tried referring to them as repeating fields, but I can't get to anything other than the first and last member in the tribe.

Ultimately, I'll be looking to have FileMaker write an email to the chief's email address and then pop in the contents of this member information field as the body.

The problem is that I can't seem to find any way to grab all of the contents of a portal, or have a calculation field grab all the entries for all the records related.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here's how I did something like this. It's pretty easy. For simplicity, I assume only three fields called FirstName, Lastname, and Address, but you can just add more fields to the calculated field to extend the process easily.

Add an unstored calculated field to the Members database that combines all the fields you want, separated by TAB characters. Something like this:

calcMemberInfo = FirstName & "{type ctrl-tab key}" & LastName & "{type ctrl-tab key} & Address & "¶"
(where it says {type ctrl-tab key} that means actually hold down the ctrl key and hit the tab key while editing the calculation. That's how you insert tabs into a field or calculation in FileMaker. Using the tab and return characters is not required, but makes it very convenient for the recipient of the email to copy and paste the data into a spreadsheet, or import it into a database of their own. Try it, you'll like the results.)
Add a script like this to your Members file:

# Members Loop
#This script builds a single text field containing all the information for all members of one Chief's tribe
#in a tab-delimited text format that can be emailed or whatever.
#First line initializes the field for the next loop, and also creates column headers for the different values,
#so the recipient will know what data is actually contained.
set field [globalMemberConcat,"FirstName {tab character} LastName {tab character} Address¶"  ]
goto record/request [First]
  set field [globalMemberConcat, globalMemberConcat & calcMemberInfo ]
  goto record/request [Next, Exit After Last]
exit loop

From the Chiefs table, make similar script that loops through all the Chiefs, and for each Chief uses "Go to related records" to jump to the members of that chief's tribe, then calls the Members Loop script to build the information in the global field, and finally sends the email to the Chief's address.
Something like this:

# Chiefs Loop
# this loops through all the Chiefs, builds the membership info for each one, and sends them an email.
goto record/request [First]
  goto related record [Chiefs-to-Members]
  perform script [subscripts, External: "Members Loop"]
  send mail[ "This is your membership list"]  
#use the contents of the related field Chiefs-to-Members::globalMemberConcat for the body of the email
#use the Chief's email address field for the recipient of the email
  goto record/request [Next, Exit After Last]
exit loop

You can do a find in the Chiefs table first if you only want to send the data to a subset of your Chiefs.

One caveat: If you're using FileMaker 6 or earlier, the size limit for a FileMaker text field is 64,000 bytes. FileMaker 7 doesn't have this limit, so if you're using that you'll have no problem.
If you have a very large number of members for each chief, it's possible you could exceed this limit. In that case, you can work around the problem by changing the Members script to do an Export to a known preset filename instead of the loop described above, and adding the exported filename as an attachment to the email. Depending on how savvy your users are, this might even be a better approach regardless of the size of the data.

Sorry, I made a BIG goof... The "loop" step should be AFTER the "goto first record" step in BOTH of the
scripts I described.

Where it shows this:
goto record/request [First]

it should show this:
goto record/request [First]

That's what happens when you cut and paste too early in the morning (I have not had my caffeine yet this morning... :P)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
neilticktinAuthor Commented:
I had to tweak this some to iron out some issues, but ultimately was able to get it going.

Thanks for putting me on the right path.
Glad to be of assistance!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.