Scripts and external db/tables

[pcourterelle]

Is it possible to define a FM script in one table/database that looks in a second table/database and then updates information in the second database based on the search results?

here's the background: working on an ad ordering & produciton status project. Ads can be ordered for up to 26 issues using a single form. The order form will be static, that is the information will not change unless the contract conditions change. The production criteria (ad size, placement, proofing, material requirements) for each of the 26 ads can change for each issue. In other words, a client can contract for 26 issues and submit a different ad for each issue.

A simple relationship between ad order and production status won't work since the production info will be constantly changing and there will be no way to back track changes from back issues.

The match field between the ad order and production dbs is the ad number concatenated to the Issue number: for example NH04-12345 -10, where the last two digits are the issue number.

The bottom half of the AdOrder db will be the interface for ad sales to input the production info. I could separate these functions but from a work perspective this is an effecient way to handle the ordering.

Here's what Iwant the script todo:

From AdOrder db, search the Production db for adnum + current issue,

If search is zero, than create new record based on adnumber and current issue and then pass info from AdOrder db to Production db.

If serach is not zero, update the record matching the adnumber and current issue with the new information.

Thoughts?/

Your feedback will be greatly appreciated.

philcourterelle

[Ender]

It's not clear what information is in each table involved here, and what infomation would need to get passed between them, but you can certainly set something up with FileMaker scripting and relationships. No Applescript is needed.

If a relationship exists between the two tables, you can access related fields and use Set Field [ ] to copy data between them. If a relationship does not exist, you can create one between a global in the table you're in and a match field in the second table.

You can script the whole process to create records or update records based on your criteria.

If an "update" means pulling over many fields, then performing an import of the record may be easier (with the option to update based on your match field).

By the way, knowing your FMP version would be helpful here. Can you update your profile?

[pcourterelle]

Basically two dbs...ad ordering, production related via a ad num that is unique to each ad. This is a parent-child relationship where the production db is dependent on the ad ordering db for primary data.

Eventually I did use setfield in a script to copy production related data (size of the ad, type of ad, where it's coming from, how the arriving and what the client wants done for proofs) from the ad ordering db. This seems clunky to me and poor design. Is it? For each new issue the ad sales staff will enter new production info and a new record in the production db will be created. If they change info then the info will be copied into the existing production record.

pc

[Fenton]

First, as Ender said, this certainly doesn't belong in the AppleScript section (but I don't know how to move it; it doesn't really matter, as many of us look at all posts). And it would be helpful to know your version of FileMaker.

Ender also mentioned "related fields." If ALL data entry (for this relevant data) is into Order, then there is no reason to have any of it as defined fields in Production. It would be redundant. Whenever you have redundant data you're opening up a can of worms. If the data is not edited in Production it should not be a field in Production. Especially in this case, where it is not prime searchable data (like a client name, which would not likely change, by the way), it's just details.

It can be shown on the layout in Production as related fields. It could even be editable from there; but it doesn't sound like it should be. The only data they really need in common is the ID.

You had best explain this:

"A simple relationship between ad order and production status won't work since the production info will be constantly changing and there will be no way to back track changes from back issues."

It sounds like to me that you have an Order, which could be for one issue, or could be for many issues (one ad or many ads?). I don't know how you could have different details for many issues without a "line items" table. You would need to store the details for even 1 Ad order in the line items file, else the structure becomes awkward. Basically the Order is just a shell, with the Client and some payment and date fields; all the details are in the line items table.

So, are you using Production as the line items table? That's what it sounds like. Or do you have some kind of "dual" system, with Order, OrderLineItems, and Production (line items)?