lookup

[transit]

This Is my first time so please be gentle !

I have a db for an autoparts catalogue were the prices of the parts are updated on a very reguler basis.

I have a db of invoices that uses the autopart id code for the relationship between the two db's

I have chosen not to use a portal to import the cost of the parts because as i understand when i change the part cost in the catalogue it would update the part costs in all the previous invoices, which of cause is not what i would want.

So i have used a lookup relationship to put the information from the catalogue into the invoices. to do this i have had to create a lot of separate relationships for each lookup of each part. This is a long process when the repair of a vehicle can easily use 20 to 30 parts.

So to the question.

Is there an easier way to do these lookups without having to create 20 to 30 relationships.

Many thanks in advance

[jasonwood]

You DO want to use a portal in your invoices BUT your portal will not be to the parts database directly, it will be to a new "line items" database.

Line items will be created whenever you enter a part number in the portal, and each line will in turn lookup the rest of the information from the parts database.

So when you create your new line items database, it will have invoice number (for the relationship with invoices), part number (for the relationship to parts), and then a bunch of lookup fields like Description, Cost, Price, Qty, etc.

In your Invoice you will make a portal to this database using the relationship based on invoice number, and be sure to "Allow creation of related records" in the relationships setup. This allows you to create line items by entering them in the portal.

You can then insert calculation fields in your invoices that calculate totals (for example: Sum(Line Items::Price) will sum up the price field of all related line items).

Give this a go and let us know if you run into trouble!

[transit]

Thanks for that Jason

It has taken me a while to get around to look at this(work and all that) and yes it worked for me!

Just one question, you end up with a line items db that in time is going to get very large,and you cant delete as that would affect you invoices, so what do you do when it gets to big?

many thanks

[jasonwood]

I just let it get bigger? Anyone else have any comments?

[kenneth2k1]

One method I have adapted is having one file for line items, and it is related to a products file. The invoice file will be related to the line items file. This should also maintain the integrity of your historical data.

As far as size, well I haven't seen this as a problem, and I set one solution up that has 975 customers with about 5900 total invoices. The amount of line items I can't remember.

For a good invoicing example, check out the FileMaker Solution Framework that is available for download with the trial version of FM at FileMaker.com

[BobWeaver]

I just let it get bigger? Anyone else have any comments?

If it gets to the point where the files approach the 2GB limit, or you find that operations are getting very slow, you can start archiving and deleting. If you have your relationship from the main file to the line items file set to delete related records when you delete the parent record, then it's very easy to keep everything in order. Just archive your records, and then delete the old invoice records in the main file that you don't want anymore. The line items will automatically delete. This is okay because they are just part of the invoice even though they are in a separate file.

On the subject of archiving, I usually prefer the simple approach of just using a backup of all the files for the archive. Otherwise, archiving schemes can get very complex.