Here's a good one for the group, cause it's kind of tough. Let's say
I've got a database with two tables, one called Contacts and one
called Categories. Categories is used to categorize contacts, so the
user can make as many categories as he wants and assign as many
contacts as he wants -- a many-to-many relation where the contact pk
(a field called IDC) is stored in a list in Categories (a field called
IDCs) through a relation we'll call CIDC_GIDCs (that is, the Contact
IDC field to the Category IDCs field).

My client wants to be able to send lists of contacts to people without
access to the database using Excel so recipients can manipulate the
data however they need. There's no problem doing this. However, she
wants to include the categories for each contact in columns so that
the rows can be sorted and compared by category. In other words, she
wants an Excel list that might look something like this:


Name Tall Senior Scholarship
Jack Tall Scholarship
Mary Senior Scholarship
Linda Tall Senior
Steve Senior
Hugh Scholarship


Since the lists of contacts she exports are determined by ad hoc
criteria, she has to be able to export random found sets from the
Contacts table. If I include a field like List(CIDC_GIDCs::Category),
it exports the categories one under the other so that each contact
takes up more than one row and it's not sortable. If I include a
field with this calculation substituting tabs for line breaks, it
exports without the tabs so that all the categories end up in one
field/column (and this wouldn't work in the end, because columns
wouldn't line up properly).

Instead, I created a bunch of generic category fields in Contacts and
wrote a script that parses the categories each time she wants to
export a list. In other words, the Contacts table has a bunch of
fields named "Cat1," "Cat2," Cat3" etc. and I use the "Set Field by
Name" step to loop through and insert categories on an as-needed
basis, then wipe them clean. The generic fields aren't used to store
data -- just to create a spreadsheet-like arrangement out of related
data for exporting.

It's a great script, but takes forever. The client has a very fast
setup, but it takes about a minute per contact record. That's a hell
of a long time! Roughly speaking, it works like this:

1. From the Contacts table, go to the Category records
related to the current found set of contacts

2. Loop through the categories and make a list of them so
we know what categories to include on the spreadsheet

3. Go back to Contacts and loop through each record to
set the generic category field as follows:

- start with the first category in list compiled in step 2
- go to first contact record
- if category is assigned to contact, insert it in first
category field, otherwise leave it empty
- go to next record and repeat
- at the end of doing this for first category in the list, do it
for the second and repeat

4. When finished assigning categories, save the records as
an Excel spreadsheet

5. Clean up the category fields and return to zero.

There's plenty of error-checking to make sure nothing funny happens
and a progress bar to keep the user apprised, but this is the basic
routine. I'd be happy to post the entire thing if someone wanted to
look at it more closely. The question is: Can the spreadsheet be
created in a more straightforward way? Is there some kind of export
I'm not aware of that wouldn't take so long? One of the challenges is
that the columns need to line up in Excel, so the script needs to take
account of all the categories assigned to the current found set and
leave empty cells where categories aren't assigned. Her lists often
involve thousands of contacts, so it needs to run faster. Seems like
there must be a better way. You say?

Thanks for any help.

Best,
-Jahn

On Apr 14, 5:06*pm, jahn <jahnbigbo... (AT) yahoo (DOT) com> wrote:
Jahn,

Make each Cat field an unstored calc field =
GetValue(List(related::Categories); n ) with n being the Cat# field
(1,2,3, etc). The highest Cat# field with a value will be the number
of categories assigned to a Contact so you don't have to worry about
the horizontal scrolling. Yes, there will be some performance issues
when you export, but it should be better than what you have. Unstored
calculations have no effect on performance except when they're being
evaluated.

G

HI, Grip -

Thanks for the input. I'm not sure I understand this correctly. I
need to have the categories line up in a column, so that if two
contacts are in the list being exported and one has the categories
blue, green and red assigned while the other has green, orange and
yellow, then the total number of categories is five (blue, green, red,
orange, yellow) and the first contact would have the columns blue,
green, empty, red, empty, empty while the second would have empty,
green, empty, empty, yellow. This has to be the case for all
contacts, so that yellow for one is in the same column as yellow for
every other. But I think your calculation only takes into account the
categories that are assigned to a particular contact, not all contacts
in the current found set. In that situation, the above spreadsheet of
two contacts would list different categories in a single column.

Is that right, or am I missing the way it works?

Thanks again.

Best,
-J.

On Apr 15, 12:42*pm, jahn <jahnbigbo... (AT) yahoo (DOT) com> wrote:
Jahn,

You're right, I misunderstood what you wanted to accomplish in terms
of the empty columns.

What you can do depends a lot on what version of Filemaker you're
using. Advanced or Regular? 9? 10? 11?

I would recommend not using a many-to-many relationship, but add a
join table of Contact Categories which simply is the key from Contact
and the key from Categories.

To solve your problem:
1) You'll need to know which categories are being used by a group of
Contacts, which can be done by script or custom function.

2) You'll want to have each "Cat#" field test for the existence of a
related value in that list of categories.

Create your Cat# fields with the unstored calc of
Let([
testVal = GetValue($$cat; #);
allCats = List(categories::name);
thisVal = FilterValues(allCats; testVal)];
thisVal
)

Without an Advanced version, your script is
clear $$cat variable
go to related records (match all) from your Contacts found set
go to the first record and loop through all the Category records
building a list of Categories in a variable (Set Variable[$$cats; $
$cats & ¶ & Category])
go to your original layout

Your Cat# fields should now show a value for the categories it has.

Hope that helps...

G

Hi, Grip -

I'm using FMP 10 Advanced -- I should've said that at the outset.
Your suggestion with the variable works like a charm. Damn, very
clean. Nicely done. I wish FileMaker had a function that referenced
the name of the field in which the calculation is stored -- kind of
like the Self function for layout objects -- so that I only had to
write the calculation once for each field, rather than adjusting the
iteration number each time, but otherwise it's pretty streamlined.

Sweet. Thanks for the help.

Best,
-J.

On Apr 15, 8:14*pm, jahn <jahnbigbo... (AT) yahoo (DOT) com> wrote:
You're welcome.

There's tricks to make it faster...like instead of calling List() in
each calc field, set a $$var to List() once in the script and call
that variable in the field defs.

G