Relationships and calcs to solve Invoice example?

[Grundly]

I'm attaching a tiny 3-table Invoice example file I need help with (both a .zip and .fmp12). Maybe my (database) problems can be solved with a good, healthy relationship, and calcs?

 

Considerations:

• Product table could have 1000s of items

• There could be thousands of Invoices, each with dozens of InvoiceLines, for a given Invoice Date range

 

Needs:

• For any given handful of Invoices, Found by Invoice Date range, I need to compute a list of which Products are ordered (Their IDs), and also the total Quantity of each.

• I need these answers stored in fields, somewhere, so I can later reference these answers with scripts.

• I need to know how to receive the answers "permanently stored" like a snapshot frozen in time, but also, "live", meaning they could update in realtime as InvoiceLine records change. I need both.

 

Wishes:

• My first preference would be to discover how to do this without ExecuteSQL. I realize it can probably be done with Execute SQL. I don't know SQL, I don't like SQL, and I can't learn SQL. If there is absolutely no other possible, conceivable way whatsoever, then let's try ExecuteSQL as the very last resort, it'll solve my problem (a good thing), but I won't understand it.

• I would prefer to be able to calculate the answers without having to go physically to a subsummary layout. But if that's the only way, let's see how that can work.

Thank you in advance.

SumProject.fmp12.zip

[Josh Ormond]

Let me ask you this, it's kind of what comment was asking, but I'll see if I can ask it a different way so you don't have to provide too much info. But the answer should give us a clue about what approach may be best.

The basic question is: Why do you need them stored? Here is the gist of what I need to know, to know how to make a recommendation:

• Is there a specific business use-case that requires those values to be stored someplace? As opposed to grabbing the info only when you need them. I can think of a few reasons why they need to be stored, but I need to know that you HAVE to have them stored the way you describe. It looks almost like you want a comparison to see a history of changes. Is that the case?
• If you don't really need a 'history', but instead are just storing a value ( your snap-shot in time ) so you can compare it to the actual/current state of the invoice...something like that?

The reason for the questions, is that they are 2 very different approaches. I'd hate to send you down scenario 1, if you are only after scenario 2. Then again, there may be an even easier way if neither are what you are doing, and it's scenario 3.

Also, when you say you need to reference them "later", how much later are we talking? Like, later in the script, in a few days/months/years?

Edited October 21, 2015 by Josh Ormond

[Lee Smith]

SumProject.fmp12.zip

 

Are these two files the same except the .zip ext? 

Edited October 21, 2015 by Lee Smith

[Grundly]

Yes, same file, just one with .zip. Thanks for looking at it.

I'll try to answer the other questions posed as well: At the moment, I don't need the "history" aspect mentioned in Scenario1. Just a live query that can provide the live total as it is at the moment it is glanced at (or when the script is run, if it ends up being a script). Meanwhile, one or more of the Line Items could change its Quantity out from under us, so this new live query should reflect that change (perhaps via script-triggered if it ends up being a script), or perhaps with nothing more than a screen refresh (if it ends up being some kind of function / calculation). 

I think, I believe... the output might need to live in a new portal.. In that portal, each line would represent one Product, its Product ID, and the sum total of Qty for that product.

For example, if my Invoice Lines from Invoices in the given date range contain Melons and Berries but not necessarily Bananas, my current Result in the portal would only have two records, one for Melons, one for Berries, and on each one, their respective total quantity sums. There wouldn't be a row for Bananas if there were no Bananas on any Line Items on any Invoices form the Date range.

I realize I may not be clearly explaining the problem, it's hard to describe. Let me know if that made it any clearer. Thanks.

[dwdata]

Hi Grundly,

If you just need s a quick report on Inventory List based on the fount set of invoices, you can use the the relationship that you already have "Invoices" ---> "Invoice_Items". You can script a Go To Related Records ["Invoice Items"] based, then navigate to a sub-summary layout. If you need to store the results somewhere, you will need to create a table for it, export the the sub-summary records set (to temp directory) and then re-import it back into that table. You can then loo for way to access the table data - maybe through relationships.

Here's a mod of your file.

 

Hope this helps!

SumProject.zip

Edited October 21, 2015 by dwdata

[Kevin Mullins]

Working from the aspect of needing historical data and reporting at different levels I would suggest a data warehouse model.  When each invoice is complete you copy the invoice line item values and the foreign keys to another table or better a "reporting database" and then you can do all your reporting from a date bound table that can easily summarize by product, customer, day, month or anything.  It is all point in time data, but will include any current invoice that has been completed.

Kevin

[OlgerDiekstra]

The invoice and it's lineitems should never change once the invoice goes active. The purpose of an invoice is to provide a list of items a customer purchased. A list of invoices therefore provides an overview of items sold within a time frame. Independent of when you ran a report for the same (past) period, the result should always be the same. If the customer wants a change made to an invoice, you create a credit invoice and then create a new amended invoice. You can add notes to each invoice to clarify. That provides a historical overview what happened.

Which means you really only need a list layout that you can filter based on various criteria. Specify a daterange and you'll get a list of invoices within that range, with cost, discounts, deposits and what nots. If you need to know what products have been sold during a period, you create a list layout based on the lineitems, and filter based on the invoicedates. Using subsummaries, you can break down the list into various categories if needed (ie month or week or day, or location).

[bruceR]

The invoice and it's lineitems should never change once the invoice goes active. The purpose of an invoice is to provide a list of items a customer purchased. A list of invoices therefore provides an overview of items sold within a time frame. Independent of when you ran a report for the same (past) period, the result should always be the same. If the customer wants a change made to an invoice, you create a credit invoice and then create a new amended invoice. You can add notes to each invoice to clarify. That provides a historical overview what happened.

Which means you really only need a list layout that you can filter based on various criteria. Specify a daterange and you'll get a list of invoices within that range, with cost, discounts, deposits and what nots. If you need to know what products have been sold during a period, you create a list layout based on the lineitems, and filter based on the invoicedates. Using subsummaries, you can break down the list into various categories if needed (ie month or week or day, or location).

Not really.

See concepts of data warehousing, including the idea of ETL - Extract, Transform, Load; and Dimensional Modeling.

https://en.wikipedia.org/wiki/Extract,_transform,_load

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237

http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/1118530802/ref=pd_sim_14_4?ie=UTF8&refRID=1G0121539DVB63151QHP&dpID=51KKZI%2BlqkL&dpSrc=sims&preST=_AC_UL160_SR128%2C160_

[Grundly]

If you just need s a quick report on Inventory List based on the fount set of invoices, you can use the the relationship that you already have "Invoices" ---> "Invoice_Items". You can script a Go To Related Records ["Invoice Items"] based, then navigate to a sub-summary layout. If you need to store the results somewhere, you will need to create a table for it, export the the sub-summary records set (to temp directory) and then re-import it back into that table. You can then loo for way to access the table data - maybe through relationships

Thank you dwdata, I appreciate you taking the time to mod my example file. This is useful information, which gets me a bit closer to my goal, and I can surely use this information for some other to-do's on my list.

I was hoping to accomplish the task without having a subsummary layout, and without having to physically go to a subsummary layout and back. But maybe that's the only way?

I am also curious if there's another way rather than exporting a file, then re-importing file, then cleaning up the file (will it do that by itself?), making a round trip to the user's file system and back.

Interesting brain food, thanks!

Working from the aspect of needing historical data and reporting at different levels I would suggest a data warehouse model.  When each invoice is complete you copy the invoice line item values and the foreign keys to another table or better a "reporting database" and then you can do all your reporting from a date bound table that can easily summarize by product, customer, day, month or anything.  It is all point in time data, but will include any current invoice that has been completed.

Thanks, Kevin for this interesting angle. That could also work. However, I would also like to be able to get "the answers" "live", before an Invoice is 'completed', before it is warehoused into a separate table. I understand how easy it would be to perform a simple Find on that new extra table after-the-fact. This gets me 50% closer though, which is great. 

[Grundly]

The invoice and it's lineitems should never change once the invoice goes active. The purpose of an invoice is to provide a list of items a customer purchased. A list of invoices therefore provides an overview of items sold within a time frame. Independent of when you ran a report for the same (past) period, the result should always be the same. If the customer wants a change made to an invoice, you create a credit invoice and then create a new amended invoice. You can add notes to each invoice to clarify. That provides a historical overview what happened.

Which means you really only need a list layout that you can filter based on various criteria. Specify a daterange and you'll get a list of invoices within that range, with cost, discounts, deposits and what nots. If you need to know what products have been sold during a period, you create a list layout based on the lineitems, and filter based on the invoicedates. Using subsummaries, you can break down the list into various categories if needed (ie month or week or day, or location).

Thanks OlgerDiekstra for taking the time to consider my solution. I do agree, generally, it would be bad business juju to change an Invoice's Line Items, and the need to preserve historical data. But in this case, they can and do change, and so we need a "live" answer, a calculation or triggered script (or CF?) that can keep up with Line Items that can change anytime. In this solution I'm not at liberty to describe why these Line Items are allowed to change, but they are, and they do, that's what we have to work with. A Line item can disappear, a new one could be added, or an existing one's Qty can change, hence I need the answer to be "live", rather than historical.

Tell me more about your Line Item List layout idea. The word "subsummary" is mentioned again, you're the third person to mention it. Do you mean create a subsummary layout, physically go there, and sort it? (Something I was originally hoping to avoid). 

Ideally, I think I need the answers in real fields, that don't have to live on a special layout which needs to be visited and sorted in order to calculate. These could be real fields alone in the Invoice table, or they could be fields from records in some yet-to-be-created live temp table of some sort, so they can be displayed in a portal... like:

>If my Invoice Lines from Invoices in the given date range contain Melons and Berries but not necessarily Bananas, my current Result in the portal would only have two records, one for Melons, one for Berries, and on each one, their respective total quantity sums. There wouldn't be a row for Bananas if there were no Bananas on any Line Items on any Invoices form the Date range.

Ideally, the answers could be seen while on the Invoice layout, not just only on a separate, specially sorted, subsummary layout.

[Josh Ormond]

Grundly,

Take a look at the GetSummary () function. See if that's something you can use.

What is the reason you don't want to use a subsummary layout? Are you running into performance issues with it? Or do you just feel like it's too hackerish?

Edited October 22, 2015 by Josh Ormond

[Grundly]

Grundly,

Take a look at the GetSummary () function. See if that's something you can use.

What is the reason you don't want to use a subsummary layout? Are you running into performance issues with it? Or do you just feel like it's too hackerish?

Josh, I feel as if the GetSummary() function just might be exactly what I need. I'm trying to work with it. It allows me to get the same result without having to fuss with a dedicated layout?

Glad you asked. I suspect, and I could be wrong, that there may eventually be a performance penalty associated with i) going to a special layout ii) performing a find there iii) sorting the results and iv) somehow ggoing-aorund-the-layout-and-collecting-the-results* and taking them somewhere else afterwards for further processing - as opposed to some perhaps some kind of live calculation/relationship setup that can achieve the same result, which my "future processing" could easily reference. Maybe it's just not possible? It's the question of the week for me, for sure.

*going-aorund-the-layout-and-collecting-the-results = I am not sure how I would go about this. Suppose we go with the subsummary layout, and suppose I have a script, that in order to do what ever it needs to do, it first needs to know "How many Melons do we have, from InvoiceLines belonging to Invoices with the date range of X..Y" ? If I had a portal with the answers in it, my script could easily go to those records and Find the answer. but how would my script find the answer when a whole subsummary layout is in the middle of everything? Hence I was trying to avoid SubSummary layouts, but perhaps I am wrong for trying to avoid them - maybe it's easier than I thought to get the answers into a future script...

One other thought "against" the subsummary layout, the results wouldn't be exactly live, like a dedicated calculation/relationship, in as much as each time I want updated numbers, I'd have to perform the whole song-and-dance round trip to and from the subsummary layout to get the latest results. Right?

Edited October 22, 2015 by Grundly

[bruceR]

GetSummary requires a found set.

So, in other words, you are back to the problem identified by Comment:

What exactly are you trying to do?

[OlgerDiekstra]

Thanks OlgerDiekstra for taking the time to consider my solution. I do agree, generally, it would be bad business juju to change an Invoice's Line Items, and the need to preserve historical data. But in this case, they can and do change, and so we need a "live" answer, a calculation or triggered script (or CF?) that can keep up with Line Items that can change anytime. In this solution I'm not at liberty to describe why these Line Items are allowed to change, but they are, and they do, that's what we have to work with. A Line item can disappear, a new one could be added, or an existing one's Qty can change, hence I need the answer to be "live", rather than historical.

Tell me more about your Line Item List layout idea. The word "subsummary" is mentioned again, you're the third person to mention it. Do you mean create a subsummary layout, physically go there, and sort it? (Something I was originally hoping to avoid). 

Ideally, I think I need the answers in real fields, that don't have to live on a special layout which needs to be visited and sorted in order to calculate. These could be real fields alone in the Invoice table, or they could be fields from records in some yet-to-be-created live temp table of some sort, so they can be displayed in a portal... like:

>If my Invoice Lines from Invoices in the given date range contain Melons and Berries but not necessarily Bananas, my current Result in the portal would only have two records, one for Melons, one for Berries, and on each one, their respective total quantity sums. There wouldn't be a row for Bananas if there were no Bananas on any Line Items on any Invoices form the Date range.

Ideally, the answers could be seen while on the Invoice layout, not just only on a separate, specially sorted, subsummary layout.

If lineitems can change, but invoices themselves do not, a better approach might be to not allow changes to lineitems, but add and mark them as deleted instead. Ie, If a lineitem changes (either the quantity, product or whatever), rather mark that lineitem as obsolete or deleted (without actually deleting it) and add a new lineitem with the new details. You can then also record the time/date a lineitem was rendered obsolete and when running reports you can take that into account. That preserves the history. By default you would not show deleted lineitems unless you search for a specific period.

Essentially (this is how it works here anyway, in Australia, and in Europe no different) an invoice is essentially a legal document between seller and purchaser, and is required by taxation offices as proof, for taxes collected/paid, and income, etc. So changing invoices along the way means your system can't be used for that purpose.

If you wanted to search for all products sold in a set period, you can create a list layout based on the lineitems. Your lineitems table needs to contain summary fields that total quantity, and cost, and then you can create a subsummary part that is sorted on products and contains the lineitem products and summary fields. When you find all items within a date range, you can then sort based on product, which will show only those products that are sold in the specified period. Remember to delete the body part as you don't need that. Dwdata already created a similar layout in his amended file. You can create scripts that take the hassle out of sorting/filtering.

If you are on an invoice layout, then all data in portals is generally related to that invoice, ie lineitems associated with that invoice. If you wanted to display all lineitems in a period in a portal, you'd have to create a cardesian relationship to a lineitems TO to show all lineitems, and then filter that portal based upon your date criteria. But that doesn't give you summarised data. 

 

Not really.

See concepts of data warehousing, including the idea of ETL - Extract, Transform, Load; and Dimensional Modeling.

https://en.wikipedia.org/wiki/Extract,_transform,_load

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237

http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/1118530802/ref=pd_sim_14_4?ie=UTF8&refRID=1G0121539DVB63151QHP&dpID=51KKZI%2BlqkL&dpSrc=sims&preST=_AC_UL160_SR128%2C160_

I'm afraid I'm not following how data warehousing is relevant to my statement about how invoices/lineitems would generally be used? If invoices do not change anymore once created, there is no need to "extract, transform and load" as everything can remain in FM. Why go to the effort if you can filter/sort and summarize to get the info you need?

[bruceR]

It all still remains in FileMaker.

Because you can't. 

Take a look at dimensional analysis concepts.

[OlgerDiekstra]

It all still remains in FileMaker.

Because you can't. 

Take a look at dimensional analysis concepts.

I'm doing it already though. 285,000 customers, 275,000 invoices. 515,000 lineitems. I'm using a list layout with subsummary report to filter out what I want to see. I can filter based on invoice types, status, dates, amounts, anything I want or a combination of these. Sure, depending on how I filter, it'll take a few minutes to got through all the data, but it's not that bad. It's also not something we do every day. We don't change an invoice once it's gone past the creation stage. If changes need to be made, we create a new invoice with new details and credit is transferred. 

Our invoices remain active for years for a lot of customers (customers pre-pay products, which can take years. they pay a fixed amount weekly or fortnightly), so exporting to a separate table for historic purposes and to keep the active invoice table lean doesn't really work for us. Especially since we want to be able to filter all invoices, rather than just active or completed.

I'll have a closer look at the concepts regardless, if for no other reason than to expand my knowledge and understanding. There's always plenty to learn (too much unfortunately).

[bruceR]

One of the basic ideas of data warehousing and dimensional reporting is (to greatly oversimplify) to create somewhat flattened, derived tables, where some thought has been given to the kinds of units (dimensions) to be reported on so that various report formats and analytical results are more easily produced.

"So exporting to a separate table for historic purposes ... doesn't really work for us."

Perhaps because you aren't using these reporting concepts. This may lead you to conclude that you've been working too hard to produce the result you describe.

Edited October 23, 2015 by BruceR

[Steve Martino]

Unrelated, but this is the second post where responders have referenced Comments comments, yet I don't see his post in the thread.

[Josh Ormond]

He had posted in another thread. This thread, I believe is a "relaunch" of the original thread. Which is not currently visible...at least not that I see.

[dwdata]

Grundy,

Here is another mod of your sample file using the "dreaded" ExecuteSQL() functions and a bit of WebViewer magic to product a dynamic list of Products and Total Quantities. It responds to your FoundSet. Special thanks To Beverly for debugging my FMP SQL (sigh).

To be upfront, I am not sure how this will work of larger sets of data - I will leave that up to you to test. Please report back if you go this route.

Hope this helps!

SumProject2.zip

[bcooney]

My issue is somewhat related to this discussion, so I haven't started a new thread.

Client wants to produce Sales Order & Invoices showing both the line item in its original state: qty unit price, etc. and the revised line item (changed qty, price).

"By default you would not show deleted lineitems unless you search for a specific period." -- as mentioned above is not an option for me.

I'm struggling with the math. Total Sales Order = summary = line_item::total_of_extension. Let's say user clicks Edit on a line item and I duplicate the line item and mark the duplicate as "obsolete."  I need the "obsolete" line item to be excluded from the summary calc. 

My instincts say there's a better way than a mess of conditional calc fields.

[Josh Ormond]

In some other projects, I've just included a field to flag the transaction ( boolean ). I tend to lean toward updating the Invoice total through a script and locking down the editing of values so that they can only be changed in certain circumstances. Instead of having a straight calculation.

That way all of the logic when processing transactions in the database are right there in the script. It's fast, and I don't have as much to worry about with WAN performance.

[dwdata]

new field:

if(excluded = 1, "", total_of_extension)

then SUM that field.

maybe...

Edited November 12, 2015 by dwdata

[bcooney]

Yes, I'm going with a boolean flag on the line_item, is_obsolete.

Josh, what always worries me about scripting the math is that I cannot trace how the numbers were produced. I can follow a calculation. But, I will consider this.

dwdata, I've started along that path. It's gotten complicated fast and that tells me it's not the best way. Perhaps using SUM ( parent_to_child::ext) will be better than using a summary in the child.

I need to still show extension on the obsolete line item.

[Josh Ormond]

It's an adjustment, for sure. But the calculation still exists in the script. If it's something where there are a lot of transactions that make up a total, I keep a transaction log. It's easy enough to either script a validation of the number, or test the balance against the expected value/test value.