Dear All I am using FilemakerPro 8.5v1 on an Intel iMac running Mac OS X 10.4.11, with no current p

[philipcaplan]

Dear All

I am using FilemakerPro 8.5v1 on an Intel iMac running Mac OS X 10.4.11, with no current plans to upgrade either!!

I have a database with a single Table called "Table1", with about 500 records in it at present, maybe 2000 more to be added in the future.

I have about 100 fields in this table called, for example, "YA01 Clothes" or "BY20 Home Decoration and Wallpapers". In other words, each field name starts with 2 uppercase letters (A-Z), followed by 2 digits (0-9), and then a space and then a **varying** number of letters.

Note that I ensure that the first 4 characters of each fieldname are unique to that table.

More such fields may be entered in the future, but will always follow the same naming-structure, and will always appear on the Layout called "checkbox layout".

There is also a field called "Status" which can contain the text OK or other text such as Pending or Cancelled.

This field appears on a layout called "checkbox layout" together with all the other ~100 fields each of which has a checkbox, and each record can have any number (or none) of these checked (this is done manually).

What I need is a script that will step through ALL the records and do the following:

(1) Perform a find in the first field (say "YA01 Clothes") and find all the records which have that field 'checked'. Then do an Export of those records into a tab-delimited file called "ya01.php", in a folder on the desktop of my Mac called "EXPORTS" (overwriting any of that name already existing there) **AS LONG AS** the Status field contains "OK".

(2) Repeat for each of the 100 or so different fields.

(3) End the script when all are done (perhaps by speaking "Finished").

Can some kind person please help/provide me with a script (suitable for FilemakerPro 8.5v1) that will do what I need.

If necessary, I can pay!

Many thanks. PHILIP

[comment]

This doesn't look like a good starting point. I am not sure what does a record in your table represent, but it seems fairly certain that instead of the multiple fields you should be using records in a related table. Most likely there would be a table for the categories, and a join table to record the status of an object in a category.

[philipcaplan]

I understand that my database-design is not "good", but I started with FM many years ago at version 2, using it essentially to massage data, and only really learned the "one big table with everything in it" approach that was needed in those days, and haven't advanced much since!

A "record" represents one of many 100's of retailers, and the checkboxed fields show which category of goods they supply.

I realize that in my description I left out one vital part of Step (1) the Export is of another field called "Export Data" which appears in every record (it's a calculated field which concatenates a number of different other fields to make a string of data which can be fed into another system, eg tagged QuarkXPress text).

I understand that using separate Tables would make for a better structure, but I have already spent many hours checking the checkboxes on about 500 records, and so splitting into 2 tables would probably be a major no-no!

This is not meant to be a "database" product that I can share with other people, just a way of getting the data out into tab-delimited format so that it can be used elsewhere.

Do you mean by "not a good starting point" that 2 tables would make it easier to step through all the fields?

If what I seek is not practical the way I have things set up at present, a script that Exports just the set of "Export Data" fields from a single Find will do, and I'll have to create a seperate one for each field, and then perhaps string them together with a script containing a sequence of "Perform Script" steps!

Any help appreciated. PHILIP

[comment]

I have already spent many hours checking the checkboxes on about 500 records

And you are very likely to spend even more trying to figure out this problem - and then again whenever you add another category. That's what good structure is all about: it makes it easy to manipulate data.

[bruceR]

"I understand that using separate Tables would make for a better structure, but I have already spent many hours checking the checkboxes on about 500 records, and so splitting into 2 tables would probably be a major no-no!"

There is no basis for this statement.

Doing it right is what you need to do. There IS a way to move your existing data without retyping it.

"Any help appreciated. PHILIP"

The help you're going to get is insistence that you fix the design problem.

Edited March 4, 2010 by Guest

[Vaughan]

I am using FilemakerPro 8.5v1 on an Intel iMac

At least patch it to 8.5v2.