Sum of sold Products ?

[tmas73]

Hi,

How can I add up all the sold products?

So if I sell 2 products to one client and I sell 5 products of the same kind to a different client how can I add up all the sold products.

Is the picture enough or should I post a file?

[Fitch]

I'll use the common terms Invoice and Line Items (which I *think* correspond to your sales_materials and sales_items).

Go to a layout based on Line Items, and search for that product. Create a summary field that is the total of the price and put that field on the layout in a grand summary part.

That's one way to do it.

[*susan*]

As is often the case, the answer depends.

If you want to know the all-time count of inventory sold, you will need a relationship:

name: inv_LineItems|inventoryID

Inventory --< LineItems, where inventoryID==inventoryID.

Then in Inventory, create a new field:

_c_countSold = sum ( inv_LineItems|inventoryID::QtySold ), calculation, number, don't store results

However, if you want to know your sales for a particular time frame, you will want to build a sub-summary report in LineItems and use a summary field to give you your subtotals.

The clearer you are about what you want your final results to be, the better we can help.

*susan*

p.s. I have to admit that in my inventory systems, I store the total sold [as opposed to having a calc] since over time, the calculation can become very slow if the number of items sold is large. So again, "it depends."

[tmas73]

OK here the file it might be easier and you see what I try to fix.

Log: 1

Pass :2

Sales_Relation.fp7.zip

[*susan*]

tmas,

At the risk of repeating myself, can you describe what you are trying to accomplish?

1. Do you want a sum of qty sold to date?

2. Total qty sold by time period?

3. Are you creating a report, or do you want your users to be able to see this number on screen?

The best method for everything depends on how you want to use it.

*susan*

[tmas73]

Sorry about that.

1. Sum of all Products sold and subtracted from total stock with products left.

2. The user should see how many products are left and how many were sold. He should see it and also be able to print a report of the sales. (Time period sounds like a good idea)

3. Product layout also should display the contact the product was sold to.

Does this help?

[*susan*]

tmas,

Now that you have defined what you need, you can probably already build what you need.

In Inventory:

qtyPurchased

qtySold

qtyOnHand

you can display your inventory in a list view for a quick look at your inventory.

A detail Inventory page can have a portal to your LineItems, showing your sales history including purchaser.

For reports, generally, you will have an easier time of it, if you build the report in the child [LineItem.] The user can query the sales date, 1/1/2009...3/31/2009, and you can print/pdf a subsummary report for that time period.

Good luck!

*susan*

[tmas73]

Thanks susan but what tables do I have to connect in order to get the calculations going? If you look at my sample I have the fields already there the problem I'm facing is my lack of relationships to get this working.

[*susan*]

Before you get too far along, you might want to reconsider your Relationship Graph organization and your table names. To maintain a system over time, the clearer everything is, the easier it is to continue to work and develop a file.

I simply added two relationships... What I think of as Inventory::LineItems, plus another to see the purchaser name. I kept the relationships I created in grey so that you can find it easily.

Good luck,!

*susan*

Sales_Relation_PCS_01.fp7.zip

[tmas73]

Great, thats perfect. The naming convention should be better once I implemented this fix! Thanks for your help!

T