calculation of total based on portal

[Katherine1812]

Hi,

I'm humanitarian, can't think like programmer. Sorry. Need to do the following:

- I have a portal (Sales Orders table) where I enter info about the books sold

- I have Inventory table where I get information about all books received/returned/sold

- I need to get EXACT amount of all the same books listed in the portal, under different invoice numbers. For example, on Invoice #123 I have 1st item "ABC book", sold quantity = 2, 2nd item "XYZ book", sold quantity = 3; on Invoice #124 I have 1st item "XYZ book", sold quantity = 1, 2nd item "ABC book", sold quantity = 1.

RPOBLEM with my calculations: whatever I do I either recieve the total of ALL the books on that invoice (#123 gets total "5"), or it gives me total of only the same books on ONE invoice (e.g., it shows total "3" of "XYZ book" instead of "4").

I feel hopeless. I hope someone will suggest what calculation to use.

Thank you so much in advance!

Katherine

[Søren Dyhr]

You see the related data from the Inventory side of the many2many relationship:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

--sd

[Katherine1812]

Dear Soren,

Thank you.

However, your database shows total of each invoice for "price total", while I need the "quantity total", listed separately on different table.

So let's say you need to show on a separate table your total of "Watcha-ma-call-it". On your first invoice you have "quantity = 2", on your next it's "quantity = 6". How to calculate total of all the "Watcha-ma-call-it" on a separate table, and have it adjusted there every time you enter it on new invoice? (E.g., if your total inventory for "W-ma-c-it" is 100, now it should show 92, then if I sell one more, it should change to 91, etc...)

Does it make any sense?

I really appreciate if you can help me with this. My database looks fine and everything else works ok but I'm stuck because of this big problem with calculation. :

Katherina

[Søren Dyhr]

I've just made an extra portal in the product's table as well as an aggregate function over the relation - does it bring you any nearer?? I haven't build in a stock level, which "just" is to use it in the aggregate calc' as well.

However does real solutions use this:

http://www.jonathanstark.com/downloads/Inventory.fp7.zip

...until it can't scale any more, and the transaction model needs to get pulled in, but for small mom-and-pop shops is the recursive structure best.

--sd

BTW!

I'm humanitarian, can't think like programmer

Rubish! Alistair Cambell, Machiavelli or Kafka are all both! If we take Albert Schweitzers:

"Humanitarianism consists in never sacrificing a human being to a purpose."

Development of software is hardly ever such deliberate sacrificing, it produces just as many jobs as it creates redundancies. Allocation of intellectual or physical resources is the lifeblood of economy as such. Florence Nightingale was indeed programmed self sacrififice ...

JMOjoinprint..zip

[Katherine1812]

Well...

It's still the same. When I use your calculation (= 0 - Quantity Consumed - Quantity Invoiced...) I still get only ONE item counted. This calculation doesn't takes in consideration all the same items on other invoices.

I've tried "Sum ()", "Count ()", all possible calculations that I could understand - but I NEVER got the result summarizing ALL the SAME items on the portals of different invoices.

Still crying... :

Katherina

[Søren Dyhr]

This calculation doesn't takes in consideration all the same items on other invoices

Doesn't it, are we looking at the same layout? Just made few changes more!

Alright I think I know what you're after, you wish to have it all on the invoicing side of the matter, but such calc's using selfjoins in each portalrow are initially swift enough, but as soon as the measure of the data grows, will screen rendering be as slow as molases, it's important to restrain your utilization of the unstored fields aggregates over a relation is likely to produce.

If you need such figures on the invoicing side of the matter, must it be scripted and a summary report which is send to the layout as text in a single field.

Here is one way of doing it:

http://www.kevinfrank.com/download/kf-fast-summary.zip

--sd

joinprintJMOmod2.zip

[Katherine1812]

Dear Soren,

Unfortunately, I understand only about 50% you're saying (no offense, it's not your fault, it's mine!)... :

So do you suggest creating a separate layout where I'll be listing all the same items from portals on Sales Orders table?

You know, it just occur to me that may be I built up the relationships in my database wrong way and this is the reason my calculation doesn't work. I've checked your relationships, your table seems to reflect the "quantity" numbers in the portal, right?

It's still unclear to me how to make it right.

The only solution I see is creating separate invoices for EACH item, but in this case I won't need portals at all. And my database will run to gigabites of information. Logically, customers often buy more than one book, so it will be at least inconvenient creating new invoice for each item.

If you could suggest anything else, I really appreciate it.

Katherine

[comment]

Assuming you have the standard structure of Invoices, LineItems and Products, you can see how many of each product were sold in total by defining a calculation field in the Products table =

Sum ( LineItems::Quantity )

You would place this field on a list layout of Products to get an overview.

Note that this will show the total quantity sold at all times - and accordingly will get slower as the number of sales increases. You may want to define another relationship between Products and a new occurrence of LineItems, and filter this relationship by date range, for example.

Another option is to produce a report from the LineItems table, sub-summarized by Product. Use a summary field (Total of Quantity). This will be seen only in Preview mode (or printed), but it has the advantage of being able to refine your find much more precisely.

[Søren Dyhr]

it just occur to me that may be I built up the relationships in my database wrong way and this is the reason my calculation doesn't work

Indeed they're not arbitrarily chosen, couldn't you upload a clone of you file, and I'll throw in the relations Michael and I suggests.

The only solution I see is creating separate invoices for EACH item, but in this case I won't need portals at all

No-one needs to do that, it's just a tiny detail you've missed somewhere!

--sd

[Katherine1812]

Good afternoon!

Just came to work, glad to see 2 responses.

Soren, Michael, here are clones of my files - Inventory and Sales Records, please take a look. (don't judge too hard, I'm really a novice!)... The only problem is that the field names are in Russian (but everything behind it is in English)

So the field that I'm struggling with is "Quantity in Stock TOTAL" (bottom right of Inventory Received layout).

Again, everything looks very amature, so I really appreciate any of your suggestions. As of now, it seems like some important layouts are missing, while too much extra junk is not needed there.

Thank you!

Katherine

Inventory_Clone.zip

Sales_Orders_Clone.zip

[comment]

First, I would suggest you keep everything in a single file - things are much more simple this way, especially at the beginning.

Next, and much more important: you absolutely CANNOT use repeating fields to hold your Sales Order items. You MUST have a separate LineItems table for this, and use a portal to that table to enter individual items. Otherwise you won't be able to make any kind of reporting or statistics, including the one you asked for.

Yes, I know you have adapted a template supplied by Filemaker. Someone at FMI should be tried and shot for this abomination. It has caused countless false starts by beginners and a considerable amount of posts here that could have been avoided.

I would recommend you start afresh with a new file, with tables for Customers, SalesOrders, LineItems and Inventory (you can use the file Søren linked to in his first post as a guide). Leave the fancy design to the end and concentrate on getting the basic structure right first.

[Katherine1812]

Thank you so much, Michael!

Ha, you recognized that FM file...

Gee... It's hard to start all over again. I'll try, anyway.

However, I'm still puzzled - will Soren's file let me see the total quantity of particular items if I do it "his" way?

Well... I was busy all day today, no chance to even touch FM files. Will start tomorrow.

Until then,

Katherine

[comment]

will Soren's file let me see the total quantity of particular items if I do it "his" way?

Yes, of course - see my first post in this thread. But that's not the only reason to do it. You won't be able to do many other things with your current structure.

[AudioFreak]

Yes, I know you have adapted a template supplied by Filemaker. Someone at FMI should be tried and shot for this abomination. It has caused countless false starts by beginners and a considerable amount of posts here that could have been avoided.

No Doubt. Or at least put in text at the top of the layout. "This is not the correct way to structure a file of this type. This is only a starter template. Or better yet don't use the repeating fields to begin and make the template structured properly.

And yes I was a victum of this when I first started using filemaker. As soon as I decided I wanted to sort the items, I was like hmmmmmm how do I do this. Went from proud of what I had to ******* I have to redo this file. Which now would take a matter of a few minutes, but for a noob. They are looking at hours and hours of learning.

Michael

[Katherine1812]

Soren, Michael, thank you!

I'm working on it now, based on your suggestion. Of course, it's slow but I hope you'll help when I scream of helplessness.

As of now... Will you please help me with the following issue? It's based on logic and I'm really bad at logical thinking... :

So after I'm done with all the OTHER suppliers books, I need to take care of our own books (the ones we publish). So my table structure for "received-sold-returned" won't work in this case. Because we don't really "receive" our books but more importantly, this time it's not us who sells or/and sends books back but it's our partners who sell them and/or return our books back to us.

So what's the solution? To create a separate database (or table)? Does it need to be in relationship with statistics table of "other people books"? Any other suggestions?

The FM world wouldn't survive without you!

(either woudl I)

Katherine

[comment]

A little more information could be useful. Offhand, I would say that while selling books you should treat yourself like just another publisher, and while publishing your store is just one of the stores that sell your books. But this seems to be an accounting decision, not a data model problem.

[Katherine1812]

Excellent idea!! Why I didn't think of it?! Seems like the most logical and simple solution.

Thank you so much.

I'm working on my database now, kind of trying to adjust it to Soren's example.

I do have a question about "JOIN" table. I'm really confused here. What do these lines mean:

- kf_infoice_ID

- kf_product_ID

They show meaningless numbers to me. Of course, all this makes sense, I simply can't understand how it works.

I know, guys, you're tired of me but one more time, please... :

Katherine

[Søren Dyhr]

...unless other booksellers are being used as storage, and when big orders are landed, the association of sellers join effords to meet demands.

I would think a publisher, very much would like to know when to initiate the making of a new edition. This would elevate the datamodelling out of the just a single attribute in difference such as consignment vs. genuine ownership of a certain title - to have something as an option to exploit or reject.

--sd

[Søren Dyhr]

They show meaningless numbers to me

They should be to most of us anyway, they are put there in order to link a join record to the order and the items are their unique made by serial numbers issued when the record was made in either table.

This means if you decrease in the kf_infoice_ID field by one, makes it leave the Invoice where it originally belonged to, and moves it to Invoice issued just before the present.

This means that these "numbers" tells filemaker with which set of records in the product and invoice tabels the join record is linked to.

--sd