Statements

[LaRetta]

Overview: We have standard Invoice to LineItems setup. Payments are applied against an invoice when the payment is made by adding a LineItem (Type = Payment) and the Invoice number is inserted. Credit Memos, Returns, NSF charges and other adjustments are also entered into LineItems and Invoice number is set to 0 (meaning yet unapplied). We can have multiple payments and adjustments against an individual Invoice. We can’t apply adjustments against an invoice until the payment comes in because we don’t know when the store will take the credit. When a credit is taken, we change the invoice number (in the LineItem credit item) from 0 to the number, thus applying it.

Request: Generate statements which COMBINE the invoice and only certain LineItems so it contains only lines similar to (I’m listing them as lines but they would be columns)

Line Type: INV (Invoice), CM (Credit Memo), etc.

Date: (if invoice, use ShipDate, otherwise use LineItem TransDate)

Amount: Invoice original charge 850.00 (planted static in invoice at moment of shipment) or credit memo amount (-110.00)

This statement must include any unapplied adjustments (without an Invoice number). Since these statement lines combine Invoice table and parts of LineItems table, I know they can’t be portal but neither can they be a list from LineItems because we don’t want all LineItems (it may have several lines of stock – we only one ONE invoice charge line. We also need the ability to omit certain lines individually from the Statement display. All I can of is to create a Statements table and write static the Invoice line and also write all payments and adjustments and use that for statements. Even if I split out adjustments into separate table, I couldn’t then combine them with Invoice lines because the adjustment won't have an invoice number yet!

Am I stuck writing the necessary data to a Statements table?

LaRetta

[LaRetta]

I'm attaching a sample of our current structure with some generic data to help anyone considering assistance. I just can't imagine how to pull this off using only relationships. Currently, we have various reports but that doesn't give us a Statement like we need!

Oh. We need the ability to click any line and omit it from the Statement display (and associated grand total).

UPDATE: I thought I needed this to happen from LineItems. I have a flag field Original Invoice Item (this currently sets via script when Invoice shipped). My thought was to flag only the FIRST LineItem from the Invoice and use this one unique 'lineitem' to represent the Invoice line. Then use a calculation to display the Invoice total on that one item line instead of stock.

Statements.zip

Edited July 29, 2006 by Guest
Added Update

[LaRetta]

I think I have it unless someone has better suggestions. I will need to set the first LineItem added onto an invoice as a Statement Item.

Then use conditional display for the comment, date and amount. When we want a Contact Statement (or via portal), I can isolate 'statement' items and show the conditional display. After trying the button on Contacts, show all records to see what is displayed or omitted. Other ideas/approaches appreciated. But we flag items as Original Invoice Items when they ship. I can flag the first lineitem to represent the 'invoice' line.

Why is it that I can agonize for hours but one hour after posting THEN ideas come to me?

Other approaches welcome. I'm still unsure this is the best method.

StatementsREV.zip

[Søren Dyhr]

Why is it that I can agonize for hours but one hour after posting THEN ideas come to me?

We all feel that way I guess! Because something happens unconciously inside your head, when transforming a felt need to a written message. Remember when you felt an urge to bring "Special Edition Using Filemaker 7" as audiobook for hiking or mountaineering - which I disuaded you from?

When being busy elsewhere, than being absolutely dedicated to the frontmost task at hand, your subconciousness continues working on the problem, sometimes even better!

I feel doing the dishes, yes we have no dishwasher ...convey the good ideas, we need combine high touch things with the high tech we wrap around us, the urge to be on 24/7 is a genuine underachiever syndrome, that ignores knowledge or virtue can't come to you via deliberate actions - but instead say by almost idle fooling around on the campus for 4-5 years or bantering mates.

Maybe we instead should call it digestion of metaphors and allegories - because when it comes to it is our brain similar to our intestines, not one single straight galvanized tube of thru-put!

--sd

[LaRetta]

:yep: Well said, Soren! I no longer attempt to rock climb and study FM simultaneously! I even gave up studying while driving. ROFLAMO!

Well it still bothers me that I have to set yet ANOTHER field via script (even though it's the one time I MUST handle all Invoice & LineItems anyway (setting as original invoice item, writing the SaleTotal and NetTotal, posting and so on). Sometimes we apply credits directly onto an order so the CM may be OrigInvItem but should still show in statements but that can be handled within the StatementID auto-enter (because it's not referring to a field in another table or another record). This CM would not be included in the InvoiceSaleTotal (only the NetTotal).

I had the (un)bright idea that the Invoice could see the first related LineItem record (that relationship is not sorted) and when the first LineItem was created, it could look at that unstored calc in Invoice (cFirstOrigItem) and if LineItems::LineItemID = Invoices::cFirstOrigItem it could use that within the StatementID (in the auto-enter) or at least auto-enter a 1 in StatementLine so StatementID could use THAT in its auto-enter. If I could get that first Item to flag itself then I might be able to eliminate the StatementLine entirely AND eliminate one more field to set during posting. But I couldn't get it to work.

I only want the first LineItem to look at an unstored calc in Invoices ONCE - when created. But I fear that, if the first stock item is deleted, and a second item already exists then THAT LineItem certainly wouldn't flag itself so the StatementID would not update properly. You see the catch-22? This particular problem appears to be a timing and perception issue and I'm still not tickled with the results I came up with.

LaRetta

[Søren Dyhr]

The bare though of being urged to flag something on childside, is more than enough incentment to make my head spin, since this resembles book keeping would both summaries as well as aggregate be sacrice. '

Generate statements which COMBINE the invoice and only certain LineItems so it contains only lines similar to (I’m listing them as lines but they would be columns)

Would it mean that you not start from the top of the invoice and pluck line one by one???

--sd

[Søren Dyhr]

My thought was to flag only the FIRST LineItem from the Invoice and use this one unique 'lineitem' to represent the Invoice line

I allowed myself to brainstorm - Not that it matters in your context, but take a look at what a difference in the logic it makes to let the ForeignKey be responsible for the autoenter of the keying.

--sd

LaRe.zip

[LaRetta]

Hi Soren,

Nor am I crazy about flagging. But all businesses flag (or should) at one point or the other - by flagging, I mean marking a record as posted, inserting a post date or otherwise freezing records from future modification. This is when I planned to mark one stock item to represent the 'Invoice'. I'm having a bit of struggle understanding you or the purpose of the demo.

I'm even less crazy about an unstored calculation which will need calculate for displaying the Invoice line (in LineItems). It will need to display for 65,000 records (that's how many invoices we have). But I don't know what else to do! An Invoice may have 10 lines of stock and 3 lines of Payments made at different times. I considered handling it all at Statement request time - looping and omitting all but one stock item but I thought GTRR would be quickest which requires the StatementID be indexable and it can't be if it looks for its sister-entries either to via lineitems self-join or looks to the Invoice.

BTW, I've seen this type of display for statements in PeachTree and AccPac (accounting programs which produce statements). Of course, THEY write everything as static lines in GL and it is easy to display only certain lines because they only write what they want and the Invoice line is ONE charge so they display all lines. If you are suggesting a better approach then please, please say more. Maybe I should forget using GTRR and just perform search on unstored calc. But we have 500,000 LineItems and I shiver at the thought of performing an unstored search in it. The ONLY other way I can see is to have Payments as Repeating Fields right within the Invoice. Oh, I HAD to add that ... the devil made me say it.

LaRetta

[Søren Dyhr]

Ha, ha! I'll see if my weird idea brings something up usefull, perhaps if you told me further about your idea with singling out the first invoice line???

--sd

[LaRetta]

"perhaps if you told me further about your idea with singling out the first invoice line?"

Well, I mentioned it as my (un)bright idea. If you look at StatementLine you will see the old calc I tried. It works wonderfully - but won't store. ^)

It works great but can't be stored (of course). And the StatementID which needs to use it MUST be indexable. This is the catch. I originally didn't have a StatementLine - I wanted to auto-enter or Lookup as the lines were created. It works fine for all LineItems except the one to single out the first STOCK item listed. Even so, if that first lineitem was deleted (before shipping) it would break. But I have scripted deletion so if User deleted a LineItem which was that 'first' Invoice line, it could grab the next (maybe using GetNthRecord()? But I can't get it to set to begin with.

I would dearly love to eliminate setting that stock field. All other entries will work fine but not isolating the first stock item. I created a self-join on LineItems - it failed because it too must be unstored. I think I'm pretty clever (smile) to use LineItems and just replace the description. It would give me what I need. But it feels convoluted AND means marking a record. In truth, the 'marking' will take place anyway because we freeze orders the moment they go out the door (post them) so I shouldn't be so fussy. But I hate marking a record if unnecessary. Is it necessary? Or is there a better way.

I very much appreciate your help, Soren. :wink2:

[Fenton]

An old classic method of setting a "Mark1st" field is to use a lookup, based on a self-relationship (self-InvoiceID, or a variation thereof in this case). You create a dummy Null field for it to lookup, which is empty. Then, in the Lookup definition, you use the: If no match, use: 1 (just type it in). The lookup will always fail on the 1st entry for an Invoice, but never afterwards, so it will enter 1 the 1st time, then lookup nothing the rest of the time (method from John Mark Osborne).

A more modern technique would be an auto-enter calculation:

Case ( serialID = self_Invoice::serialID; 1 (or an ID), "")

with "[ ] Do not replace existing value" unchecked

Both methods require scripting any Delete. Which should test for the mark, then reset the 1st Invoice record, which could be targeted with a self-relationship (providing you're on one of the Invoice line items; or you could capture it into a global before deletion and use that. You might want to check that there is no other mark first, though there should never be.

I agree with hating to mark records, trap for deletion, etc.. Not that it so difficult, but that it creates a vulnerability, however slight. Have you tried a Find on the unstored value. It might not take as long as you think. I've been pretty happy with the relative speed of unstored Finds in FileMaker 8.

[LaRetta]

ROCKIN!! The lookup method worked! What a great idea to identify the first entry! I could not get the auto-enter to work. I used the same self-join LineItems::InvoiceID = selfjoin::InvoiceID that I used for the Lookup. I set Auto-Enter (Replace) with: Case ( InvoiceID =selfjoin::InvoiceID; 1; ""). Nada. I even tried LineItemID = selfjoin::LineItemID which should produce boolean 1 and still nada. Nothing seems to work on auto-enter. But Lookup works!? Why is that? I wonder if it has to do with Auto-Enter and Commit of the record. There appears to be a difference between Lookup and Auto-Enter here that I'd sure like to understand.

Are you suggesting a find replace the GTRR? Wouldn't I have to still mark the first stock item to isolate it? Otherwise, I would have to find, then omit all stock but one. And I would still need to use a calculation to replace the one stock display, right? Finds are currently dawgs - we are still on vs. 8.0v2. But I know finds were much faster on 8.0v1 and I've heard the speed is back in 8.0v3. I 1) thought GTRR would be faster anyway and 2) couldn't figure how to isolate only one stock item in the find anyway. Maybe you are suggesting I let the StatementID be unstored and find on that? Now THAT might be worth it. But I'm open if you have more suggestions!

I've already won, ya see? Because I'm bringing this design in for a landing and these are the last few displays required. I'm not done yet but I want to party before the finish line ... :smile2:

[Fenton]

But Lookup works!? Why is that? I wonder if it has to do with Auto-Enter and Commit of the record. There appears to be a difference between Lookup and Auto-Enter here that I'd sure like to understand.

As would I. It is subtle, they definitely trigger differently. And it has changed since FileMaker 6. Lookups have remained much the same, but auto-enter calculations have become more difficult to trigger; a slight change in the timing. I was hoping that would work for you, by unchecking the [ ] Do not replace; apparently not. It may be that you need to throw in a Modification Date/time field to trigger, or evaluate, or something. Sorry, a little vague.

I would recommend upgrading to 8.0v3. I don't know if it is as fast as 8.0v1, but it seems pretty good. I saw some unstored Finds that just plain didn't work in 8.0v1. At least that's what I remember. One of my clients (you know, the Oregon people) is doing an unstored Find, with calculations, on over 700,000 records. It's about 4 minutes.

Well, I've got to go. One of my clients just got around to sending me some files, which must be upgraded and back in the office by morning. Sigh -|

[Søren Dyhr]

Lookups have remained much the same, but auto-enter calculations have become more difficult to trigger; a slight change in the timing.

Is the field creation order still an issue??

--sd

[LaRetta]

8.0v2 fixed that, Soren.

2.2.1. Fields that have auto-entered calculations depending on other fields no longer rely on field order creation for consistent results.

But I can't make it work in 7.0v3 either. I wouldn't have thought (Replace) would make a difference since this is an original set upon record creation and Replace would only be helpful for changing the value (but of course it couldn't change the value after set because it refers to related table). But with (or without Replace), the puppy won't fire. What DOES work on auto-enter to fire the StatementLine:

Let ( trigger = creationTimeStamp ; InvoiceID =LineItems 2::InvoiceID )

But ONLY if I DON'T uncheck 'Do Not Replace Existing Values'. With 'Do Not Replace' unchecked, it inserts a 1 upon record creation but changes it to 0 after commit. But then in both instances, it also puts a 1 on all associated Lineitems. Duplicating StatementLine and using that makes no difference.

If I use the unstored calc in Invoices, it won't work it either case, ie, it inserts a 0 - meaning (I believe) that it is evaluating before it can be seen through the relationship. Whether I use CreationTS or ModificationTS makes no difference; nor using Replace)

Let (

trigger = creationTimeStamp ;

LineItemID = Invoices::cFirstOrigItem

)

God, I love this stuff. But I sure wish I understood exactly what was happening here.

[Søren Dyhr]

8.0v2 fixed that

What I seemed to remember!

--sd