What's the Best Way

[KathySL]

I hope I can ask this question in simple terms. I have a business in which customers often buy more than one product. I have created a line items file which consists of one record per item ordered on an invoice. Each record includes item ordered, the cost per item, the sales tax per item, qty per item, price per item (qty * cost), and shipping per item.

I also have a invoices file that looks at all the above field via a portal. I set it up this way so I could "see" what my invoice looks like as I input items orderd. The invoices file also has summary fields that are adding up the totals for costs of items, total tax, and total shipping.

Here is the problem, I am having a hard time figuring out how to create financial reports that will show me how much I made in a give time period, how much sales tax was collected, etc. because I have part of the fields (the summary fields) in one file and the other fields in the line items. I can't create a report using calc or summary fields from different files to show up.

Am I working this the best I could or would it be better to have ALL fields in a line items file and delete the invoices file altogether? I realize I would have to be in preview mode to see any one invoice.

I appreciate any advice you can give me.

Kathy

[mse]

You are reinventing a bicycle. Look at the free FileMaker Solution Framework on the filemaker.com site

[KathySL]

I did look at that, but it doesn't show any type of reporting.

[ernst]

Hi Kathy,

Supposing every record in your invoice file is one invoice,

you could make some new sum fields in the invoice database that add up all the sum fields you already have for each invoice record.

You could perform a Find for all invoices of a certain date range to add up only the total for that date range.

Alternatively you could make yet another database which has a record for each month with fields for year, month and all your totals, and define a relation to your invoice database. The invoice database would also need a month and year field per invoice.

Hope this helps,

Regards,

Ernst.

[mse]

I did look at that, but it doesn't show any type of reporting.

The FMSF contains, along with the other features mentioned, a Reports.FP5 file (with a number of options such as time range, etc.) that you could adjust to fit your needs. I suggest you inspect this solution carefully.