find values in two tables, display results in one

[Joseph O'Brien]

Is there a good way to do a search for date ranges in two different
tables and have the results appear on the same screen/printout?

Our Filemaker database has tables for, among other things, Jobs,
Purchase Orders, and Invoices. Purchase Orders and Invoices are both
related to Jobs using the key field Job Number. Each table has a
"Date Created" field.

The sales guys want to be able to tell at a glance the total of all
the POs, Invoices, and the difference for a given period. Rather than
printing two separate lists and comparing them, they would like both
lists (all the POs and all the Invoices) to appear on the same
screen.

Is this even possible?

Thanks,
joseph

[John Weinshel]

It's possible, but not in any straightforward way I can imagine.

You could run a script, in each file, that first isolates, using a search or
a relationship, the records within the date range and then loops through
them and posts the information as parsed text (i..e, 'Invoice for Fred
Jones: 9/15/03') to an incrementing global field that lives in one,
presumably central file; loops through the same file again if there are more
dates of interest; and so on, within any file that contains relevant dates,
until a long text list is built and displayed in some viewable form.

My preference would be to use a separate 'Events.fp5' file, and add a small
record to it whenever one of the events in question take place. That
obviously implies that record creation in the other files is scripted.

The downside of an extra file is that it is just that-- more stuff. On the
other hand, if you only need it to view recent activity, you can purge it of
old records on a regular basis.

Now you can build a simple report form that shows all activity in whatever
format you like.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance


"Joseph O'Brien" <obrien1984@hotmail.com> wrote in message
news:6b6ea86e.0309181209.4c3777@posting.google.com ...

> Is there a good way to do a search for date ranges in two different
> tables and have the results appear on the same screen/printout?
>
> Our Filemaker database has tables for, among other things, Jobs,
> Purchase Orders, and Invoices. Purchase Orders and Invoices are both
> related to Jobs using the key field Job Number. Each table has a
> "Date Created" field.
>
> The sales guys want to be able to tell at a glance the total of all
> the POs, Invoices, and the difference for a given period. Rather than
> printing two separate lists and comparing them, they would like both
> lists (all the POs and all the Invoices) to appear on the same
> screen.
>
> Is this even possible?
>
> Thanks,
> joseph