Exporting all portal entries

Posted on 2004-11-03
Last Modified: 2010-04-27
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.

Question by:neilticktin
    LVL 19

    Expert Comment

    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.

    LVL 19

    Accepted Solution

    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)


    Author Comment

    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.
    LVL 19

    Expert Comment

    Glad to be of assistance!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now