Save&Restore Find criteria

[Mandu]

My interface for searching for invoices includes the usual stuff - date of order, Invoice_ID, subtotal, shipping cost, tax - as well as fields for name, address, city, state, zip.

An invoice actually contains two name&address sections: Bill To, and Ship To. I want my Find tool to bring up records in which the name & address criteria matches either the Bill To or the Ship To section.

I've got something like this:

Enter Find Mode []

Pause Script [indefinitely] allow user input

(A: save the find criteria that was entered)

Constrain Found Set [] find recs using BillTo address fields

Enter Find Mode []

(B: restore the find criteria from above)

(C: move all BillTo address criteria to equivalent ShipTo fields, blanking out the BillTo address criteria)

Extend Found Set []

That seems to me to be the right approach. But I'm having trouble with A, B, and C. Short of using a brute-force approach -- writing a separate step or two for every field on my Find layout to accomplish A and B -- is there a more general (perhaps reusable) method for recording every Field/Value pair in the layout? And, conversely, restoring the values into a new find request?

If I knew how to do A and B, I think C would be pretty obvious.

So, that's the specific task. Unless there's a different approach I've overlooked, what I think I need is a way to Save and later Restore Find criteria.

Thanks

Chap

[T-Square]

Other ideas:

If the BillTo and ShipTo areas are derived from a single client source (i.e., you select a client entry from a clients table), then I'd look at how I could search the clients table and then the related invoices. Maybe you could put in ShipToID and BillToID fields, which could be more quickly searched?

Another solution I've used is to build a search layout with global fields that then get used in the search. Not a lot easier necessarily, but if you pair this with creating a few calculation fields that concatenate the two instances of each field together (NameSearch = BillName & " " & ShipName), you can execute a single search...

David

[Mandu]

Interesting ideas; thanks. I haven't at this point got a Clients table, so the first one won't work in my case. The second approach might work.

[T-Square]

Ah, well, maybe you could create a client table; then you could select the client from a list, with all the addressing fields set as lookups to the client table. No typing of a default address; you'd only have to confirm that the destination address is the same as the auto-entered one. That's a lot less typing!

[LaRetta]

Now is the time to set your structure and set it right. If your BillTo and ShipTo addresses are RECORDS in another table related to your Customers then searching will be simple. If you don't structure your addresses as separate table, you will suddenly be faced with 1) the situation you now face regarding searching, 2) a Customer who has two separate ShipTos (their summer home and their winter home) and 3) reporting problems eventually. Or what if a Customer wants to ship to their sister in Florida? Having more than one ShipTo is very common as well. Are you going to add more fields to the Customer table?

Put 'like' records in the same table. An address, no matter its purpose, fits this category. Then simply categorize them as BillTo, ShipTo etc.

LaRetta

[Mandu]

Thanks, T-square and LaRetta. That makes a lot of sense.

I've held off designing a Customers table, because I figured that required a lot of work -- primarily, identifying duplicates (I used to write software for direct mail so I know what's involved), and handling changes-of-address and other edits (e.g. should I ever reflect the changes back to previous invoices?)

I think I will defer this "Or Ship To" address searching until I build the Customers table.

Thanks for your help.

Chap