Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #481

Re: Need advice for a FMP report

Show all headers | View raw

On Tue, 29 Nov 2011 07:08:46 -0800 (PST), Joseph O'Brien wrote:
>  In our company, a "job" may not have the same date as an invoice or
>  purchase order. A job might stay open for six months with POs and
>  invoices scattered throughout. We are interested in being able to
>  capture a monthly, quarterly, and yearly snapshot of our actual
>  billable rate. So, if we were to use the "jobs" file to run this
>  report, we would need to have some way to constrain related records by
>  date. I haven't found a way to do this in FMP6 yet. Is this possible?

The main task is to create the matching partners.

So if a record from the 2011-11-29 shall be reported for november,
create a calculatin "invoice-month" which does compute
year(recorddate)&"-"&month(recordate). 

Thus you may build a relation from 2011-11 to 2011-11

Your statistics table now needs the task to create new records for each
month, which can be scripted easily.

Quarterly can be done by year(recorddate) & " Q" &
(int(recorddate/3)+1) which will create "2011 Q1".

... just an example for a possible formatting, you may use others.

Same for the year.

(there are other possible solutions with less extra fields, but this
solution here is easier to understand)


You don't have to export anything. You may have to check whether your
statistics table actually does have a matching record. 

Example: your statistics do have a creation date, an id or anything else
which is always not empty.

Then do a find within your orders and do exclude every record which does
find a match in the statistics.

Then change a relation in order to permit creation of related records.
Typically this would be the relation from invoice-month to
statistics-month.
Enter a value which does create the related record.

Loop through all your found orders and do create a related record
whenever it is missing. 

Thus your statistics table will have all month entries which are
required to perform whatever you need.



That's the simple explanation if the date is all you need for your
statistics.

Do just the same if you need e.g. the company as an extra requirement
for your statistics.

Then you may compute everything you need via relations.

- Martin

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Find similar

Thread

Need advice for a FMP report "Joseph O'Brien" <obrien1984@hotmail.com> - 2011-11-28 14:28 -0800
  Re: Need advice for a FMP report yourname@yourisp.com (Your Name) - 2011-11-29 16:16 +1300
    Re: Need advice for a FMP report "Joseph O'Brien" <obrien1984@hotmail.com> - 2011-11-29 07:08 -0800
      Re: Need advice for a FMP report Martin Τrautmann <t-usenet@gmx.net> - 2011-11-29 15:31 +0000

csiph-web