I have a relational database with three charts. I need to parse the "name" field of the "client" chart and use it to automatically generate a series of records (invoices) in another chart, one invoice for each name in the "client" chart. I have this working manually: if I enter a client's name in the "invoice" layout, the invoice populates automatically with the rest of the client information, including billable time and such. I just want to script this invoice generating procedure so that the user doesn't have to manually create a new record and choose each client's name in turn from a client name popup. (For clients with no billable time, I figure if I get this scripted, I can have the script examine the billable hours field and discard the record if the value is zero.)

The best I've been able to do is enter static text in the client name field using the InsertCalculatedField script command or enter the textual result of some unrelated formula. When I try and couple the InsertCalculatedField or the SetField script commands with the GetField function, it doesn't work.

Can anyone help with this looping, parsing thing?

FileMaker Version: 7

Platform: Mac OS X Jaguar

I'll assume when you say "chart", you mean "table"?

If so, I think we also need to know what your third table is for - is this where billable hours comes from? and then, wouldn't the invoice correspond to the third table and not the clients (even though client information appears on the invoice).

If the invoices and clients are always 1 to 1 (which doesn't make a lot of sense, but seems to be what you're describing), then you'd do something like this...

First get a found set of clients you want to invoice, then:

Go to record, first

Loop

Set field, Invoices::any field, Invoices::any field

Go to record, next, exit after last

End Loop

since there will not already be a related invoice when you perform the set field, one will be created and the lookups will be populated (or related fields displayed).

Does that help?

Yes, sorry. I meant "tables" when I said "charts."

I'll try and be a little clearer:

This database has three tables in it.

-Table "client" lists every client, including manager, contact info, etc.

-Table "employee" lists employee info, including bill rate, hours worked, and for what client the employee worked that week (the "client" field on "employee" table is a relational link to "client" table, so the user simply chooses the client name from popup menu). This employee table is designed to look like the time cards that the employees fax, that's why hours worked and for what client are entered in the "employee" table.

-Table named "invoice" has relational links to "client" and "employee" tables. Choosing a client name from a popup menu in the "invoice" layout automatically pulls the client contact info from "client" table and also links to the "employee" chart and populates a portal in "invoice" with every employee who worked at that client site, including how many hours worked,bill rate, total due etc. One invoice is created per week, per client, listing some number of employees who were at that client site, their hours worked at what rate and a subtotal for each employee. This employee/hours/rate/subtotal info is in a multi-line portal.

This all works fine, but it requires the user manually generate each invoice by choosing "new record" in the "invoice" table and selecting each successive client from the "client" popup. What I want to do now is create a script that generates an invoice for each client who has any billable time associated with them.

The logic of what I want to do is:

-Go to the "invoice" table

-Create a new record

-Obtain the value of the first entry in the "client name" list in the "client" table

-Enter this value into the "client name" field of the "invoice" table

-Allow all the relational data to appear, completing the invoice

-Repeat for all items in the "client" table's "client name" field until a single invoice for each client has been generated.

After I get this working, I'll put something in so that if the "total due" is other than zero, the script continues with the next invoice record, else, it deletes the zero invoice record then continues with new invoice record

I hope this is clearer.

in that case, the script I gave you before should work.

I still don't understand how you avoid billing a client for the same hours every time you create invoices.

It sounds like whenever you establish a relationship between client and invoice, all the corresponding "employee" records show up as line items.

Yes, that's correct. All corresponding "employee" records _do_ show up as line items. This design is predicated on the user updating the employee records each week to ensure that they reflect that week's billing.

I must be very obtuse when it comes to scripting. I've not been able to get your suggested script to work.

This step has me confused: Set field, Invoices::any field, Invoices::any field

It sounds like this step writes a field's data to itself.

Should I be executing your script from the "clients" table (with all clients records available)? Will this script then create records in the "invoices" table? If so, should this step look something like:

Set Field [invoices::Client Name; Client List::Client Name]

It seems that my suggested step instructs FMP to set the Invoices::Client Name field to equal the value of the Client List::Client name field. It seems to my newbie brain that this step, nested in the loop you suggested, should create a new Invoice record for Client Name and populate the Invoice::Client Name field in each record with the desired client name.

Of course, my version isn't working, hence my frustration...

Yep, that's a good way to go. It's basically the same as what I suggested only it makes more sense intuitively (better).

Make sure the relationship is set to allow creation of related records in the invoices table.