Item History for Invoice DB

[Isaiah 40:31]

Okay, so I've got the four databases to make up my invoices. I even get the portal to work to list the invoice history for each customer.

My question is how do I get a display of the item history for each customer. So if, over time and several invoices, the customer has bought various items, I want it to list all the items and hopefully the quantity.

Ex:

Cereal 5

Cheese 3

Eggs (carton) 8

Milk 4

I'm using FMPro 6 Unlimited. I'm also trying to get the customer to see this result via their web browser.

invoicedb.zip

Edited July 8, 2007 by Guest

[Ender]

Try using a columnar list with sub-summary parts based in the ProductsOrdered file. You'll need a summary Total of Quantity to display the counts ordered. Place the ProductName and Summary Total on a Sub-Summary by ProductName part.

When the records are sorted by ProductName and viewed in Preview Mode or printed, the parts will show. I'm not sure what the Web Publishing differences might be.

[Isaiah 40:31]

Thank you Ender, that is a GREAT start for me. BUT I see, as much as I tried, that my question wasn't clear enough. What I am trying to do is let each customer see how many of each product they have purchased over time (over all their invoices). Since the ProductsOrdered DB has no information regarding the CustomerID I'm not able to do this from that file. I'm guessing I should be able to do it from the invoice.fp5 file, though.

So what should happen is...

Bob will know that his purchase history (of all his invoices) is:

5 loaves bread

3 cartons of milk

4 bricks of cheese

And Sally will know that over all of her purchase invoices she has bought:

6 Cartons of Milk

3 cartons of cheese

5 packages of tissue paper

9 packages of cookies

18 tabloid magazines

[Ender]

You should have your ProductOrdered include a CustomerID field that's a lookup from Invoice. Then it should all fall into place.

[Isaiah 40:31]

Yeah, I realized after I wrote that, that I would not be able to do it from the Invoice.fp5 file.

However, when I try to do it as you said, it necessitated me creating a new relationship in ProductsOrdered.fp5 between it and the Invoice.fp5 file. So I tried that, and made the CustomerID field a lookup. But it will only populate the field in ProductsOrdered.fp5 if I hit "Enter" in the Invoicedb.fp5 BEFORE I enter data in the portal. Otherwise each record in the ProductsOrdered.fp5 file will be generated without the CustomerID field populated.

[Ender]

I don't remember commits being necessary in this situation with FM5/6. I know it comes up occasionally with FM7/8 though.

If you script your navigation and record creation, you can commit the new record using Exit Record after the CustomerID has been entered, then give control back to the user to enter into the portal. This should allow the CustomerID to lookup correctly.

Depending on the workflow, it might be easiest to have the process start by finding/creating the Customer, then have a button to Add Invoice, which grabs the CustomerID and creates a new Invoice in the Invoice file, populating the CustomerID as it goes.

[Isaiah 40:31]

I was afraid you were going to say something like that! (I have no knowledge on how to make the script, as for the other suggestion, I may be able to figure it out, but I'm not sure! Sort of pushing the limits of my comprehension).

UPDATE: I have just figured out that the above is not a concern when published through the web. It populates the fields correctly without doing any scripting, etc.

(Thinking out loud) Now I need to figure out how to get the browser to ADD items to the current invoice rather than make a new invoice for every item. I have a feeling it's simply making a button to direct me to editing the current invoice, but not sure.

I see that you are very helpful around this forum, Ender, and want to thank you for your input and your time.