Conditional sum in a portal

[RDWirr]

I am trying to build onto a simple FM application to record inventory transactions for our small manufacturering facility. Records for each batch of components we make are entered into a table including the quantity, departmental source and destination of the components. I am trying to find a simple way to total quantities of each component in each department at any moment. The basic calculation is totalling the quantities of each component with a particular departmental destination and subtract from this, the total of the same component with the same department as a source.

Then I want to display the total quantity of each each component as a line item for each component in a portal that is linked to the parent product.

We were earlier doing this in an excel spreadsheet but it grew to such a huge size with all the inventory transactions, it was taking more than 20 seconds to recalc. Although I am reasonably fluent in spreadsheets, databases are new territory for me. I have attached a piece of the application with some data in it as an example of what I have. The portal I am discussing is on the inventory tab and my attempts at summing are the right two fields. I would be very grateful if someone can give some suggestion on the formulas to accomplish this.

Login as admin with no PW.

Thanks in advance,

RDW

ProdInfo.zip

[RDWirr]

I tried this a few days ago but got no response, but I am still struggling with it, so I am approaching it here a little more generically.

I need to sum all the values in a single field in a table that is related to a table in a portal and that meets another condition in the table. In my application, I have three tables, parent items, components and inventory. The component table is related to the parent table by the parent item number and the inventory table is related to the component table by the component number. The inventory table has multiple records of each component number with various qtys of the component in each record. I have a form that shows the individual parent item with a portal that displays all the components related to the parent item. I need to aggregate sum the qtys of each component from the inventory table and show them on the line item of each component they are related to in the component portal. Finally I need to condition this aggregate sum, summing or ignoring line items in the inventory table according to a field in the inventory table.

I have been trying a =sum(qty) field in the inventory table and then showing that in the portal but the portal line item only grabs the first instance of the related component for a value. That same field in the components table aggregates the total of all inventory without filtering it for the specific component.

If anyone is interested, the example file is entered under the subject "Conditional Sum in a Portal" just below.

Anyone have any idea how to do this?

Thanks in advance,

RDW

[Genx]

Could you simply reply to your other post next time if you want to get it some extra attention -- it saves us answering the question twice when we do come across it.

Now, I don't get a lot of what you said (more likely than anything else, it's just me) but a general point here: Sum should be used as follows -- Sum(RelatedTable::Field), this will sum the contents of Field for all records that are related to the current record you're on (whether that be inventory and ... the related table components?).

[Lee Smith]

I merged the two topics.

Lee

[RDWirr]

Hi Genx,

Thanks for responding and also thanks for setting me straight on the way to follow up. Yes, I know my explanation earlier was complicated. Sorry, I was trying to explain the structure.

You can see I'm pretty green with this. I used your suggestion and it works fine. Thanks.

The only thing is, I need to sum the inventory::qty but only if a condition in a related field in the inventory table is true. I have been trying to do this with an IF function like this IF(Inventory::Destination="RoughMill";Sum(Inventory::Qty);0) But this doesn't work, I get strange results. The calculation seems to grab value in the top record of the destination field to do the test for all records rather than applying the test individually on each record.

Can you give me some guidance on how to apply this condition for each record in the summed table?

Thanks again,

RDW

[RDWirr]

Thanks Lee

[comment]

There's no conditional sum in Filemaker. You'll have to make the calculation in the inventory table, e.g.

Case ( Destination = "RoughMill" ; Qty )

then point the Sum() function to this calculation field instead of Qty.

[RDWirr]

Thanks Comment!

That work great!

I hope you can bear with me for another moment. Your solution works but my criteria for the conditional sum comes from a value list that may change. Do you know of a good way to return an array of sums based on all the various criteria instances in the "Destination" field?

I know I am traveling further afield here but I eventually have to work this functionality in. I still have to figure out how to change the fields in my portal to dynamically display the available test criteria but first I have to get the underlying data.

Thanks,

RDW

[comment]

I think you could try following this thread - but be warned that (a) this is not exactly beginners' stuff, and ( it uses an undocumented feature (as explained there).

Filemaker's conventional method would be to produce a report with sub-summaries.

[RDWirr]

Hi Comment,

Yep, that's more advanced than I am at this stage. But it's good stuff that I will study and learn from. Thanks. For now, I think I'll stick with the subsummary reports.

Thanks again,

RDW