self-join

[Robbydobbs]

I have a customer that purchases many items. Each time this customer purchases an item his customer_id is on that item. Now let's say that the customer has purchased 3 items and I want to print out a Purchase Order for them. I assign the same Purchase Order number to each item so that I can tell whether or not that particular item has been invoiced. So this is how the records would look.

Customer Id Description Price Purchase Order No

---0054--------1 Computer----------500.00-------0123

---0054--------1 CD Player----------50.00-------0123

---0054--------1 TV---------------1500.00-------0123

Total Purchase Order = 2050.

What I want to do is show only the purchase order no and the total owed. Would I do this with a self-join?

[Ugo DI LUCA]

Hi Robbydobbs,

Yes.

In the line item, your calc would be :

c_totalbyPO = Sum(SelfjoinOnPO:(Price*Quantity))

c_totalitemsperPo = Count (SelfjoinOnPO::Item_Id)

....just to make sure, as there are some different terms here around the Forum,

A PO is usually what we purchase

A CustomerOrder is what the customer purchase

So if it is a PO, it could also be linked to several Customers

Then c_totalbyPobyCustomer = Sum(SelfjoinOnPobyCustomer::(quantity*price).

But I think you are referring to a Customer Order.

[Robbydobbs]

you're right, I am. Thanks for clearing that up.

[Ugo DI LUCA]

Hi,

In addition, as this wasn't clear, if you want to display these results for multiples "PO" by Client, just use a portal with relationship to the PO file.

In that case, tha calc in the PO would be

c_totalPO = Sum(::lineitems(byPO)(Quantity*Price))

Then you're portal will look like

PO#------Total PO

PO#1----1,150,00

PO#2----1,250,00

....

and the total by customer would be

Sum(PoByCustomer::c_totalPO) where PoByCustomer is a relationship from Customer db to PO db using the Customer_Id.

[danjacoby]

I'd use three files: Customers, Purchases and LineItems.

"Customers" is a list of customers, including their CustomerIDs. The data for an individual customer needs to be entered before you can create a Purchase Order for that customer.

"LineItems" is where the actual purchase data resides, but you don't enter anything here (you do, however, print the purchase orders here). The customer info exists in the form of calculation fields that pull from the related "Purchases" record.

In Purchases, each record is one Purchase Order. You enter the customer from a value list that pulls from the Customers file, so each CustomerID is correct. Then in a portal, enter the line items.

BTW, you can have a fourth file, "Items", which contains a list of all the items that can be purchased, so that when you enter the item in the portal in "Purchases", data such as the price gets pulled in automatically.