Pending invoices

[jogarcia]

Hi, I have customers invoices and invoice payment tables, my question is how I can get, in the invoice payment to show only the invoices with balance > 0.

Thanks in advance.

[mr_vodka]

This is a better technique because it is faster than searching on an unstored calc field. Otherwise you would have to do a find for > 0 in the balance field.

JMO's Overdue

[comment]

:confused:

[mr_vodka]

From what I understood from his post, he had invoices and then a related payments table. He also had a balance field which I assume would be the total amount of his invoice minus the related payments. He asked to find all invoices with balances greater than 0. So instead of just doing a find for any balances that were greater than zero, I sent him a link to JMO's demo.

[comment]

I am assuming the same thing, but I don't see how you can apply that demo to this situation.

[jogarcia]

Thank you for your response,

is there a way to create a relationship that show me in a portal in the receips file all the unpaid invoices? my problem is that calculation fields doesnt work when I create a relationship.

I set a flag in the invoice file that is a calculation, if the balance is 0 the field value is CUSTOMER CODE PLUS "N", If the balance is different than 0, then is the CUSTOMER CODE PLUS "Y" then in the Receips File I created a Flag that is the CUSTOMER CODE PLUS "Y" But the relationship is not working, then, I realize that it is because it is a calculated field in the Invoice file so it doesnt work.

What I want is to show all unpaid invoices ina portal in the Receips file, do I explain? Sorry for my english, my mother language is spanish...

I can upload the files.

[mr_vodka]

Ok. Sorry for the confusion. Now I understand what you meant by the :.

I would assume that the basic principal would be the same as it is in his instruction section in the sample. The only difference is instead of the additional criteria for days back, it would only search those invoices that were marked as "pending".

The Invoice Date auto enters the creation date and Status auto enters the “Pending”. Once an invoice is paid, the value of “Paid” is placed in the Status field"

John's demo assumes that when you apply a payment to an invoice, it checks the balance then and if there is still a balance, it will flag the status field as as "pending" rather than doing a search in a balance checking calc field which would be a lot slower.

[comment]

John's demo assumes that when you apply a payment to an invoice, it checks the balance then and if there is still a balance, it will flag the status field as as "pending"

True, but in a real implementation, when the payments are in a related table, the flag will not change automatically - unless it too is an unstored calculation.

[jogarcia]

: no ideas ?

[comment]

The simple solution is to search the unstored calculation of balance. If and when that gets too slow, you will need to devise a way to mark settled invoices. This can be done either periodically, or as a part of the process of entering payments.

[LaRetta]

Just to clarify ... it is MUCH faster to GTRR then Constrain than a straight search on an unstored calculation. Here is one test I've recently performed:

30,000 Contacts with 98,266 Invoices. Of those, 14,116 have outstanding balances.

Option 1: Switch to Invoices and search on cOutstandingBalance (unstored calculation Sum()) took 2 minutes, 36 seconds.

Option 2: GTRR (through normal relationship Contacts > Invoices) with Constrain took 45 seconds.

Option 2 script would look like (from your Contacts table):

Show All Records

Go To Related Record [ Show Only Related; Match Found Set; your Invoices table occurrence ]

Constrain Found Set [ Restore ; cOutstandingBalance > 0 ]

If you can talk that business into ditching a portal (and just open a window with the list), then your life will be easier. As it is, you MUST store the balance (or a flag of some sort) as static in Invoices so it can be indexed. It will increase your work 10-fold to create and maintain it and it is prone to get out of 'match'.

Edited February 26, 2007 by Guest

[LaRetta]

I pulled up my test. I'm unsure why I GTRR'd in the example I provided because ALL Invoices need to be searched. If you drop the GTRR and just use Constrain, then it takes 27 seconds. This is compared to a FIND with takes over two minutes. Why Constrain (preceeded with Show All Records) blows a find out of the water is unknown to me but it does. Maybe I hadn't restarted FM between that test ...

Edited February 26, 2007 by Guest

[LaRetta]

Upon review, I think my tests were flawed. They were performed while being served. Constrain() is much faster WHEN it has at least partially indexed for your base relationship. New tests (from home) and re-start of FM between each takes just as long as Find. So, depending upon whether you've been using your base relationships yet (and usually we are) then Constrain() is faster.

I need to get better at testing and considering all possibilities which might skew a test. :

[mr_vodka]

Hi LaRetta,

I still believe that the way JMO suggested with a static status boolen or text field could be the fastest. The status field would be updated upon the script executed on entering of a payment.

[jogarcia]

Ok, ad mr_vodka said, an as it seems that there is no easy way to make it i have decided to do the following:

Every time an invoice is created the user have to click a "SAVE" button so the invoice set the balance to the total of the invoice. Now, when a receipt is created, as I can pay with one receipt more than one invoice, I created a "SAVE" button that recalculate the balance of every invoice in the portal and set the current balance in the Invoice itself.

Every time the "SAVE" button is clicked, on invoices or in receipts the scrip check if the balance is 0 or not, if it is not 0 then the script make my field "IS_PAID" to CUSTOMER CODE & "UNPAID" if is paid or to CUSTOMER CODE & UNPAID if not.

Then, and is exactly what I want to get, when I get to receipt a payment from a customer I have a portal that show me only the unpaid invoices of my customer creating a relationship between Receipts and Invoices that show of my invoices all the ones of the especific customer that is also UNPAID

THe problem could be if for some reason, any of my advanced users jump from the invoice or from the receipt to another window without clicking the "SAVE" button. So I have to make some manteinance scripts that actualize the balance of all invoices from time to time.

Do I explain myself?

:

[LaRetta]

This is the problem with static balances. It sounds good in theory but that means that you must TIGHTLY control User editing. It also means that if a payment is misapplied and removed, it must again be adjusted (via script). It also means that, any time Management wants to add a simple adjustment (adding a lineitem), it must be scripted as well. If this is multi-user, you must tightly control record-lock within your script because, if the record (Invoice) is being modified by another User and you attempt to change the balance, the script will fail.

Yes John, writing static balance seems simpler. But I see nothing in John's file which indicates that was how Status was handled; in fact, considering it is a demo file designed to solve a relational issue, it is amazing that there are no relationships within it (which is the MAIN crux of the problem).

I currently use this method (writing static) but, as I've already pointed out, it is easy for it to get 'off.' And nothing is more embarrassing, jogarcia, than to have system send Past Due notice because the balance didn't update or refuse to allow a new shipment because system says they didn't pay their last bill. Or User added a product onto the invoice or changed a product amount and the balance wasn't updated. Pray you don't lose power or have computer problem. If you grab backup, you may end up after payment entered but before script changed Invoice balance! Even if the backup is okay, User could have system lock (and need to reboot) at the MOMENT of script fire (and again, the balance will be incorrect).

Add into the mix things such as credit memos. It ALL must be scripted to adjust the Invoice balance. Trap for errors constantly and lock down ALL access of adding payments/adjustments; adding lineitems onto an Invoice and so forth. And all for what? To more quickly find outstanding invoices? It sometimes is necessary but it goes against dynamic theory and simplicity, and I fight against it like a banshee. But I also know the solution belongs to the Owner and these (sometimes) are business decisions I must follow. I make it very clear that it will add greatly to the development costs.

Also plan regular mismatch checks - usually before you generate your Past Due notices. Ha ha!

UPDATE: Hey! I just realized ... maybe that's why when I get bills in the mail, it says, "If you've already made this payment, ignore this notice." Ha ha. They must write static balances.

LaRetta

Edited February 27, 2007 by Guest

[mr_vodka]

Hi LaRetta,

I used to use real balances too until people started complaining about the speed and then I changed it to static. I agree with all your points, but again I was pointing out that it was faster.

Yes John, writing static balance seems simpler. But I see nothing in John's file which indicates that was how Status was handled; in fact, considering it is a demo file designed to solve a relational issue, it is amazing that there are no relationships within it nor an explanation of how Status should be handled (which is the MAIN crux of the problem).

As for the demo, you are right in that it doesnt give you much on how status was handled. However, in his full invoicing application, which I have seen before, he has the same method of marking paid invoices. It was actually a static pulldown menu.

Hmmmm maybe if we get lucky, he will jump on this thread and explain in further detail how he usually handles payment processing, why he chose to go static, and if there was a better method possibly of handling the situation. :

UPDATE: Hey! I just realized ... maybe that's why when I get bills in the mail, it says, "If you've already made this payment, ignore this notice." Ha ha. They must write static balances.

LMAO.

[LaRetta]

I believe there is a mid point, John, as Comment pointed out as well - between static balance (and keeping it up to date) and searching on aggregate. Envision:

Invoice has Paid flag 1/0. But it is NOT handled during payments OR by Users. Instead, at month-end, fire script:

Go to Layout [ Invoices ]

Perform Find [ Paid = 1 ] ...(the field will be indexable)

Show Omitted

Constrain [ Restore ; OutstandingBalance = 0 ]

Replace Field Contents [ Invoices::Paid ; 1 ]

Any time you want to see current outstanding invoices, use your TRUE OutstandingBalance (which can be trusted). To find outstanding, you would do the same thing:

Go to Layout [ Invoices ]

Perform Find [ Paid = 1 ]

Show Omitted

Constrain [ Restore ; OutstandingBalance > 0 ]

It will be very fast but remain dynamic. This will not work, of course, if current outstanding must display in portals. But opening window list views can USUALLY replace portals.

LaRetta

[jogarcia]

Wow, I dont have words to express how good I feel that you guys have take time to help me to find a solution. you are really Filemaker Guru's.

You are right LaRetta, there are a lot of things to take in mind with static balance, How i wish it were possible to create relationships between calculated fields, I still dont understand why is not possible.

See, I am just a bit new to Filemaker pro 8.5 and havent use the new functions, I jumped from version 5.5 to 8.5.

What is the "Constrain [ Restore ; OutstandingBalance = 0 ]" about?

I am also wondering, is there a way to lock an entire record with a status field so I can avoid users making changes after It is printed?

I am rebuilding building from 0 a big system for the advertising agency I work for. We have Invoices, receive Bills, purchase orders, insertion orders, production orders, cash pettite, payroll, bank management (more than one bank account), quotes, account management, all integrated.

We would like to take the most of Filemaker 8.5 taking advantage of every new feature.

We have this system running since 1996, and decided to make it from scratch.

Thanks,

Julio.-

[LaRetta]

Hi Julio,

Constrain means take the found set and only search IT for further criteria (thus drilling down into the set). You can look it up in Help for more details. So, instead of searching your entire Invoice table each time, you search for those already flagged as paid (1) then show the omitted set which would produce those unpaid (0) as of the prior flagging process. You then Constrain() down to only those who NOW have a zero balance and flag them.

So first script is your monthly (or whenever) flagging process. Second script is the one you use to find your TRUE outstanding invoices based upon a calculation (number) in Invoices of: Sum ( LineItems::AmountTotal ) … adjusting for your real table/field names, of course. You will be searching an aggregate function here which, by its reference to another table, must be unstored. But you’ll be searching a very small set so it will be fast!

I have learned the hard way … do not ever mention that you can produce portals filled with information which must be static (because they can't be stored). Instead, simply say, “No, but I can give you a popup window with the list of them.” Dazzle, side-track and confuse if necessary to keep from being trapped into writing static as I have.

How i wish it were possible to create relationships between calculated fields

It IS possible as long as the child side of the relationship’s join field can be indexed. But Invoices are another breed, ie they base their total on ANOTHER related table (LineItems). And one table can’t keep itself updated based upon another; thus the writing static and subsequent mess. This is not a bad thing in itself; it just means we need to be more clever in our solutions. How will you know if it will be Unstored? Any field or calculation which references a field (within it) from another table, a field set to global storage, or fields which need to be hypervigilent (such as the Get() functions like Get ( CurrentDate) ) must be unstored. Containers can't be indexed either. All other fields can be indexed.

...is there a way to lock an entire record with a status field so I can avoid users making changes after It is printed?

Read up on Privilege Sets. You will find many good posts on the subject here on FM Forums. You can use that Paid flag (1) to restrict access depending upon each User privilege so only Owner and Accountant can change something after it has been presented. The moment an Invoice leaves your hands and is given to a Customer, it should be frozen from further change. ALL change must stop at the moment of posting. Personally, I lock via tight scripting and navigation, ie I test my Lock and Post flags before I allow a User to access that record’s information. Why don’t I use privilege sets on this? Because it is quite harsh (just as field-level validation is) and I try to protect my Users from the irritation of it. I want my Users happy and eager to run through the solution without fear. I use privilege locking for payroll and other highly-sensitive information (in another file).

You have an exciting prospect re-writing from scratch. There are many new wonders since vs. 7. Have fun and welcome to FM Forums!

LaRetta

[jogarcia]

wow, thank you!

I will be working on the projet this afternoon, I will let you know if I find a workaround, I need to move from the invoices thing and built other tables, is there a way I can send you these files for you to see what I am working on?

Thanks.