Jump to content

Work around for Balance Remaining field in simple invoice db


This topic is 4798 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a simple Invoice database which has a CLIENT table, an INVOICE table and a PAYMENTS table. They all have to be separate tables. After creating an invoice for a client, I can then create a payment for that invoice. If it is a partial payment then there is a Balance Remaining. All I am trying to do is create a text field which would tell me if the invoice has been paid or not (yes or no). Because the balance remaining calculates from the payments table by adding payments and subtracting from the invoice total, I cannot seem to get the paid field to work. I have tried a custom function as well. Can someone help? Thanks

Link to comment
Share on other sites

This would be an unindexed field - but it should work..

First, you may want to create at least one field - Amount Paid = Sum(Payments::Payment_Amount)

then you can create a field - Paid_In_Full =

Case(isempty(Amount_Paid);"No";

Invoice_Total = Amount_Paid;"Yes";

Invoice_Total>Amount_Paid;"Partial";

Invoice_Total<"Amount_Paid;"Overpayment")

There are a lot of other ways of doing this.

Link to comment
Share on other sites

"a text field which would tell me if the invoice has been paid or not (yes or no)"

Actually, this calls for a calc field (I call fields like this "flag" fields and start them with the prefix "flag_").

So, flag_Paid = If (BalanceDue=0, 1, ""), result number.

However, you may wish to also include a manual flag to allow for forgiveness of the balance due, "flag_MarkPaid." That'll need to be included in the flag_Paid calc:

flag_Paid = If (BalanceDue=0 or flag_MarkPaid=1, 1, ""), result number.

Link to comment
Share on other sites

These are great suggestions but what I forgot to explain was that I want to look at unpaid or outstanding invoices in one the client record. This relationship will not work because of the balance remaining calculation and the paid calculation. The portal relationship will not work since balance remaining and paid fields are UN-INDEXABLE. That is what I need help with. I would like a way to show all outstanding invoices in a portal.

Link to comment
Share on other sites

This is very easy to do in version 11, with filtered portals. In previous versions, you need a more elaborate workaround:

1. In the Invoices table define a calculation field cOutstandingID =

Case ( Sum ( Payments::Amount ) < Total ; InvoiceID )

2. Define a self-join of the Invoices table as:

Invoices::cOutstandingID = Invoices 2::InvoiceID

3. Place a portal to Invoices 2 on a layout of Clients.

Link to comment
Share on other sites

Do you know if this will work if Invoices and Clients are separate file databases?

If so, how do I put a portal from Invoices2 in clients database, realte them?

Thanks - i'm very close

This is very easy to do in version 11, with filtered portals. In previous versions, you need a more elaborate workaround:

1. In the Invoices table define a calculation field cOutstandingID =

Case ( Sum ( Payments::Amount ) < Total ; InvoiceID )

2. Define a self-join of the Invoices table as:

Invoices::cOutstandingID = Invoices 2::InvoiceID

3. Place a portal to Invoices 2 on a layout of Clients.

Link to comment
Share on other sites

Why not simply show all the related invoices in a portal on a client form? Then you can sort the portal by balance due desc or use condt'l formatting to highlight the unpaid invoices. You could also use a global field to allow the user to filter the portal and only show unpaid invoices.

Also, files are not the same as tables. Comment, I believe you took him literally here. He has separate tables, not separate FM files. Also, I believe he wanted to show invoices from Clients, not a portal of unpaid invoices on an Invoice form.

Link to comment
Share on other sites

My client has two separate databases in a old system, so yes two files - he does not want to join the files into one. So I have separated the files into CLIENTS and INVOICES but I still do not understand how I make a relationship between the CLIENTS file and the INVOICES 2 relationship in INVOICES. I have the databases to upload but do not see how I can do it - Thanks everyone

  • Like 1
Link to comment
Share on other sites

I still do not understand how I make a relationship between the CLIENTS file and the INVOICES 2 relationship in INVOICES.

Do you currently have ANY relationship between CLIENTS and INVOICES? If yes, duplicate the INVOICES table occurrence on the relationship graph, and link the duplicate to the original as explained above.

My client has two separate databases in a old system, so yes two files - he does not want to join the files into one.

Aren't you using a "new system" now? One of the great benefits of version 7 and higher vs. previous versions is the ability to have multiple tables in a single file. Unless you have some serious overriding reason, I'd suggest you take advantage of it - it makes things easier and less error-prone.

Link to comment
Share on other sites

This topic is 4798 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.