invoice/lineitem/inventory relationship

[susan siegel]

This is an update to an earlier posting with a different subject heading -- but basically the same problem.

I'm still going round in circles over how to set up related customer/inventory/lineitem/invoice files. I'm either missing some steps, doing steps out of order, or missing something altogether.

My apologies to those who have answered my earlier posting: I'm just not getting something.

I've looked through the forum but can't find any examples that explain the procedure in simple enough terms that I can follow.

Let me see if I can break it down into simple steps and questions

1. once I get the four files set up and linked appropriately on matching customer id, invoice # and product id, on what file do I begin to enter the actual new order -- invoice or lineitem -- or doesn't it make a difference?

So far, I've entered the new sales on the lineitem file, using a portal that brings in description and price for each product from the inventory file.

Two problems here:

a) for the product list (there are 35 products), I enter the code for the first product and hit enter. (I prefer to enter the code manually rather than scroll through a drop box. But could the absence of a drop box be part of my problem ??)

The above works fine -- sort of. to enter the next product, I insert a carriage return and then the next product code and description and price shows up. Fine, except that the full list of product codes doesn't show up -- which creates a problem later on for generating reports. To deal with this, I added a second product code in the portal (this may not be on the file I'm attaching), just before "descripion." Not great looking, but at least it shows, on separate lines, each product code.

the main problem then is: where to enter qty, disc and ext price for each product row? There's no visible space to enter this information for multiple rows.

The only way I fixed this -- and I know it's not the answer -- was to add qty, disc and ext price fields to the inventory file and then add them to the portal box in lineitem. This creates other problems though in the inventory file, so I don't think it's the answer.

Do I need to create a second (or third) portal? If so, where and for what field/s? It seems that creating a portal is the way to deal with multiple rows. But how can I create a portal to bring in qty and disct from another file when information this doesn't exist yet. (I read some discussion about dynamic and static data -- but this didn't help me understand what to do in practical terms.)

If - and Once I can get the qty and ext price for each of the products in the lineitem file, then should they be lookups in the invoice file (using matching invoice file #):

Or do I need one or more portals in the invoice file??

Another basic question about relationships and lookups that I might be misunderstanding: If two files are joined, e.g., customer and invoice, with matching customer ids, then on the invoice file, do name, address, etc. have to be "lookuped" to the customer file -- or does the relationship automatically pull that information into place on the invoice file? In my test cases, it seemed that I did have to use the lookup for name and address.

For reports - I assume that this should be based on data from the lineitem file?? Yes? No?

I need the usual type of reports: for a given period of time, qty and total dollar value for each product and then total values for the time period. All I need is the product code; no description. Or a description without the product one; one or the other is sufficient.

Once again, thanks in advance for helping a beginner "see the light."

As I can only attach two files, I'll attach the lineitem and invoice. They're both in rough form.

the customer file is straightfoward and I have no problem using lookup to bring in address, etc.

and the inventory file, I assume, should just be product ccode, descirp, price , so there's nothing much to see there.

line_item.zip

BHP_Invoice.zip

[caveat1]

Hello,

It seems like you might be putting fields from the inventory file into your portal on the invoice file. You need to put the fields from the line items file into the portal on the invoice. The description & price in the line items file are lookup fields, based on info from the inventory file. Quantity and discount can be fields in the line items file that you just manually enter the data.

Reports on items sold can come from the line items file, using summary functions.

Hope this helps.

KC

[susan siegel]

Thanks, KC.

I started off with fresh files following your suggestions but ran into the following problems.

1) You suggested that for the line item file:

"The description & price in the line items file are lookup fields, based on info from the inventory file."

Sounds okay - but -then how do I get the ability to add several products (rows)? In the product id field, i can add a second and third row with a carriage return -- but when the descp and price fields are seprate look up fields to the inventory file, I don't have that option -- or can't figure out how to find/get it. If I add repeat fields for descrip and price ( I don't think this is the way to go), then they just repeat the first value. Or, is there a way to change that?

2)You also suggested for the line item field, that:

"Quantity and discount can be fields in the line items file that you just manually enter the data."

Again -- how do I deal with adding rows for multiple products? I only seem to be able to create the ability to add additional rows with a portal. But as this data is first entered in the line itme file - there's no other file to create a portal from.

3) Then, for the invoice file, you suggest:

"You need to put the fields from the line items file into the portal on the invoice."

that makes sense to me -- but this assumes that I can fix the above problem so that I can have multiple rows for descp, qty, price, etc. If I can do that - then I can put them all in a new portal for the invoice file.

So the problem really boils down to the multiple row issue - or so it seems to me.

susan

[bruceR]

Enter only one product per line item record. That's the way it's done. For examples of portal use and line item records look in the Templates folder that comes with FileMaker for Event Management or Task Management.

[susan siegel]

Belated thanks for explaining the "one item" per record on the line item table. That was the piece of the puzzle I wasn't quite seeing.

If I've got things right now (I'm still doing some tests before putting the finishing touches to the layout), if a customer orders 3 items, I enter 3 separate records in the line item file, one for each item, along with the date and customer number that I use for matching fields.

Then -- in the invoice file, I've set up a portal that pulls in the 3 item descriptions, price etc.

I add the subtotal, shipping, total, etc and then generate the invoice.

I'm having a little trouble generating a report from the line item file that summarizes qty and ext price for each product id when sorted by month -- but I think that with patience I can get this worked out over time.

Thanks again.

Susan

[bruceR]

You've got part of it right. But in line items you do not need date or customer number. You need Invoice Number. The invoice has a date, invoice number, and customer number.

The customer table is related to the invoice table by customer number; the invoice table is related to the line items table by invoice number.

[susan siegel]

Thanks.

I just started to reply to your posting with more questions -- but as I wrote it out -- the light blub flashed on.

By adding an invoice # field to the lineitem table, I can then use the invoice # as a look up field on the invoice table.

It's all beginning to fall into place.

I won't be able to implement this solution right away, though as I'm working out other FM issues that have to take higher priority.

There's a lot of learning going on all at the same time -- and this forum has been invaluable. Thanks to everyone.

Susan

[Fenton]

I haven't read the whole thread/files. But this statement struck me as odd:

"I can then use the Invoice# as a lookup field on the Invoice table"

The Invoice# is the auto-enter serial ID of the Invoice table. It would not be a "lookup" field there or in InvoiceLines. It is generally passed to InvoiceLines by the "Allow creation of related records" option in the Invoice ->InvoiceLines relationship, by typing into a portal of that relationship; or by a button to create a new line (less common).

As Bruce says, the Invoice date & CustomerID are in the Invoice table. You might also have a creation date in the InvoiceLines, and you might lookup the CustomerID from Invoices, to use for reports (optional). But you would not use them for the relationship from Invoice to InvoiceLines.