Script search of value list items

[Rockxyzzy]

Gang-

This post talks a great deal about Value Lists, but I'm posting it here because my problem is really about searching. I'm trying to script successive searches for a series of items in a Value List. Creating the Value List isn't the problem. Using it as a basis for a scripted search is.

Here goes: I have a table that contains timesheet entries for a number of different clients. (Client, temp employee name, date, hours worked, rate, etc.) I have the timesheet table set up with summaries and subsummares so that if I manually search for a given client, only the timesheet entries for that client appear, the summaries and subsummaries do their job, and the report can be printed as an invoice.

To aid in this searching and printing, I have a Value List that's automatically built from the entries on the "Client" field.

Now, I need to script this process to be automatic so that the user can print all invoices for all clients with the push of a button.

My scripting approach is to:

Find all records not flagged "billed"

Find just the records associated with the first client on the client list

Sort by client to activate the subsummary

Print the invoice

Set the "billed" flag on each record that's just been printed

Repeat the process for each successive client on the Value List.

I can do this manually (except the flag, which I haven't implemented yet). The current difficulty I'm having is how to parse a Value List and do a series of searches for each client to print my invoices. My current plan (kludge) is to define a global field that contains the value of the Value List (using the valueList items function), export the list (that now represents a single entry for each client name), re-import the list as separate records in a table and use those values as a basis for my scripted searches and printings.

Except for the difficulty in turning a single field of client names, delimited by line breaks, (the result of the valuelist items script) into a tab delimited text file that I can re-import, I think this will work okay, except it's embarassingly kludged.

There has to be a better way to script a series of searches to accomplish this goal.

Does anyone have any thoughts on how this can be better accomplished?

[RalphL]

You just need another summary "client". Sort by client. You are making this too hard, let FMP do the work.

[-Queue-]

If you use a global number as an incrementor, then you can loop through the value list like

Set Field [gIncrementor; 1]

Loop

Set Field [gText; Let( V = ValueListItems( Get(FileName); "YourValueList" ); Position(

[Rockxyzzy]

I thought of doing that and you're right: doing so will provide reports that properly break the data down into client subgroupings, and with the proper subsummaries and such, I can total the various entries into one invoiced amount for each client.

The problem I'm having is that, while doing so in the way we're discussing results in accurate viewable reports, I need to print each client section as though it were a separate "invoice" document (each with its own first page header and proper formatting) so that each prints as a standalone, mailable (with the address peeking through the window envelope), invoice. I'm not sure that this is possible using just summaries (please tell me I'm wrong because I'd love to do it your way!), largely because I can have only one Title Header per report.

In short, this will work only if I can make a single report, summarized by Client, print with the appearance of separate documents, with each Client section having its own title header and footer. If you tell me how this is possible, you will be my hero for life.

[Rockxyzzy]

This looks amazing. It makes my brain hurt a bit. I'm going to print it out and study it to try and understand exactly what's going on here.

[RalphL]

Don't use a title header. Use a sub-summary for the first page as a title header. It can conatain the same info that the the title header does.

[Rockxyzzy]

Ralph, you are my hero. The sub-summary (leading) works great. Thank you. I have each sub-summary sorted by Client and they work great.

Do you have any suggestions on how I might increment an invoice number that must appear in each sub-summary, so that from session to session, the "invoice" numbers increment from the previous sub-summary?

[RalphL]

One way is to store the last invoice number in another table, usally only one record. In a multi-user setup you must lock this record when it is in use so that other users can not get the same number.

[Rockxyzzy]

This is a single-user setup, so fortunately, I don't have to worry about locking records. Would I need to create an equivalent Invoice Number field in the table that populates the line items in the invoice report and define it as a calculation field that increments based upon its counterpart single record in the other table? (Did this make sense?)

In other words, the Invoices are created by your clever reporting scheme (with sub-summaries and such) and are populated by a "Timesheet Entries" table. I'm pretty sure I can't make the invoice numbers work by just defining an Invoice number field in the Timesheet Entries table because each invoice is composed of many records from the Timesheet Entries table, e.g., there's not a 1:1 correspondence between each "invoice" and each record in the Timesheet entry.

What I need to accomplish is make the invoice number appear only in the leading sub-summary section and increment once for each leading sub-summary section. Hence my question: should I make an Invoice Number field in the Timesheet Entry table and define it as a calculation field that somehow reads the single Invoice number record in the other table, displays that in the leading sub-summary section, and increments the number and writes the incremented number back to the Invoice Number field in the other table?

Or, as before, am I making this too complicated and there's a simpler way that I'm overlooking entirely?

[RalphL]

In similar situations I have made a Work Order which is assigned a number when it is created, usually an auto-entered serial number. I use this as a key field to the line items or in your case the timesheet. I then use this number as the invoice number. This works if you only issue one invoice per job.

[Rockxyzzy]

But I'm still not clear on how to make this invoice number appear only once in each leading sub-summary section of the report and properly increment.