Invoice Payment Portal

[j4jason83]

I am trying to create a payment solution for my existing database. I have everything else working right now, but no way to keep track of payments. As it appears from my searching on Google, I need to create a join table as I have a many to many relationship. So this is what I have now.

Invoices:ID_Invoice ---<Invoice_Payments:id_invoice

Invoice_Payments:id_payment>---Payments:ID_Payment

Inside my join table, I have a field for Payment_Portion. This way I can designate how much of the payment will go to each invoice. Sometimes I get a payment for one invoice. Sometimes its for a couple. Sometimes its like a down-payment or something on that order.

On my Payments layout, I have a field to look up the Account and pull that info from the Accounts table. That works good. I created a portal to the join table (Invoice_Payments) and added fields from the Invoices table to the portal.

My problem is that I can not get anything to show up there. What I am trying to accomplish is that ANY invoice for the account above that has a balance more than zero will show up. This way I can select which invoice to apply the payment to and how much gets applied to each invoice. I have seen this done in the Core3CRM database, but since I am not looking to purchase new software at this point, I figured I could create the same thing in my database. But it has proven a little challenging right now.

I am sure this is something very simple to do, but it is above my knowledge level. Any help or guidance will be greatly appreciated. Let me know if more information is required to help.

[comment]

My problem is that I can not get anything to show up there. What I am trying to accomplish is that ANY invoice for the account above that has a balance more than zero will show up.

Where exactly is "there"? The portal, showing records from the join table, will not show anything until you have created a join record. If you want to select an invoice for this record, you will need a value list (or some other selection mechanism). And basing a value list on an unstored value (balance) is not going to be simple.

See also:

http://fmforums.com/forum/topic/76398-allocating-payments-to-invoices-questions/page__view__findpost__p__359694

[j4jason83]

Where exactly is "there"?

That's just it. There are no records in the join table. I don't know if I have to create them manually each time a new invoice is made or a payment is recieved. Or if there is a way to have this automated. I am not sure I want a valuelist showing up in the portal to choose from. I just want the portal to display all records from a particular account that have a balance. Maybe I have to have the valuelist in-order for the portal to display this?

[comment]

There are no records in the join table. I don't know if I have to create them manually each time a new invoice is made or a payment is recieved.

Neither. You need to create a new join record for every amount of a payment allocated towards an invoice. For example, if a client has 3 outstanding invoices for $100 each, and they send you $150, you need to create either 2 or 3 join records - depending on how you want the split the $150 between those 3 invoices.

Or if there is a way to have this automated.

There is always a way - provided you have very clear rules about how the amount needs to be split. Even then, don't expect this to be simple.

[j4jason83]

My thoughts are that I will go to my PAYMENTS layout, click NEW RECORD, select the ACCOUNT, the portal will populate with outstanding invoices. There is a box in the portal for amount of the payment. I click on the checkbox on the left side. Enter an amount on the right side and it will subtract that amount from the invoice total. On the layout that I posted, there is an AMOUNT field. I want to be able to enter in the total amount received (ex. $1000) and have it pull all invoices that have a balance. I can then enter in to Invoice #1, $300, Invoice #2, $200, etc until all invoices are paid or there is no money left from the payment. If there is an overpayment, then I want to somehow have that money become "unallocated" and have it for new invoices that will come.

[comment]

You are describing a user interface. Unfortunately, there's no way I could give you a step-by-step guide how to build such interface within the confines of a forum message. I will make a few points, though:

1. If you want the portal to show invoices, you must set it to show records from (an occurrence of) the Invoices table.

2. The field storing the amount allocated to an invoice cannot be in the Invoices table; it must be in the join table - though it could be shown in the portal to Invoices on the layout of Payments.

3. A further complication, already mentioned above, is that the balance of an invoice is unstored and therefore a portal showing outstanding invoices is a challenge; perhaps you could use a filtered portal for this - provided the number of records is not excessive.

[j4jason83]

1. If you want the portal to show invoices, you must set it to show records from (an occurrence of) the Invoices table.

I have a TO of my INVOICES table. The relationship diagram I posted above shows that its connected to my join table. So I believe this is already in place. Is my relationship correct? Do I need to change it?

2. The field storing the amount allocated to an invoice cannot be in the Invoices table; it must be in the join table - though it could be shown in the portal to Invoices on the layout of Payments.

This field is in my join table. I called it Payment_Portion. And on my screeenshot of the layout, its the last field under the subheading of AMOUNT. I have added another screenshot of my join table. Hope this helps.

3. A further complication, already mentioned above, is that the balance of an invoice is unstored and therefore a portal showing outstanding invoices is a challenge; perhaps you could use a filtered portal for this - provided the number of records is not excessive.

Can you explain this a little better? Wouldn't I need the invoice balance to be stored so that the database knows if its unpaid or not paid in full?

When I started this project, I started googling payments and such and ran across the Core3CRM application. Unfortunately, if I wasn't already setup with my current database, I would have thought about switching as that handles payments pretty well. I downloaded the demo and I like how they have it configured. Unfortunately, I am not really to shell out lots of money and time in switching to a new database when the one I have is working just prefect. The only thing I don't have is a way to effectively keep track of payments and which invoices have been paid in full.

I would like to be able to select the Account, enter the info for the payment and have the portal populate with the unpaid invoices. I am just not sure where to go from what I have. My join table doesn't have any records in it. I am not sure if I have to create those each time I make an invoice and receive money, or if I need to make a script that will create the join records for me. Since I am still learning FileMaker, that is why I have posted on here in the hopes that someone can walk me though and help me setup this payment portion of my database.

[stefangs]

why do you absolutely need unpaid invoices showing up in a portal when you click 'new record'? seems to me it would be easier to just look up unpaid records which isn't hard to do. you could sort (or filter) these records, for example, by customer and then go about allocating the funds you recevied.

because you probably don't know in advance how you are going to distribute x dollars in advance across x number of invoices, i think you will be challenged in coming up with a logic that automates that.

stefan

[j4jason83]

The unpaid invoices don't need to be there until I after I have selected the account to which the payment belongs. Then the portal would populate with the invoices. Do you have a sample or could you explain a little more how to go about and sort or filter the portal records?

As I mentioned before, my join table is empty. Should it be? Or will the records be there once I enter a payment?

[stefangs]

actually, i wasn't thinking about portal records, just records. but it's certainly possible to also filter/sort portals. please search the forum, there are lots of techniques for this already posted.

my skills are not the most advanced and for lack of time i'm still stuck in FM6 thinking in many respects, therefore some approaches i take feel a bit clunky and i hesitate to post ideas... but i'll try anyway:

to filter a portal, i create a global with a relationship to the child records. i can type or script the contents of the global and the portal will be filtered.

sorting is a bit trickier. i create an invisible tab panel. when you click on the portal headers, you are actually switching tabs. this can be made by aligning the tabs and portal column headers carefully. each tab contains an identical layout, but they are sorted differently. a nice side effect of this is that you can color the column with a highlight color to show by which column the list is sorted. no need for graphic calculations and very fast, of course, because no scripting is involved.

hth,

stefan

[j4jason83]

to filter a portal, i create a global with a relationship to the child records. i can type or script the contents of the global and the portal will be filtered.

Would I create the global field in the join table or in a different table? And you mentioned about creating the relationship to the child records. Isn't this done when I created the ID_INVOICE relationship?

[stefangs]

Would I create the global field in the join table or in a different table? And you mentioned about creating the relationship to the child records. Isn't this done when I created the ID_INVOICE relationship?

you'd create the global in whatever table/layout where you would like to view your portal records.

yes, a relationship of course is created like you describe, but if you use that key you can't filter or sort. for these purposes you need a different key.

e.g. invoice abc has 10 line items. they appear in whatever order you enter them, unless the relationship or portal setup sets the sort order. if you want to change the sort order, you need a different key just for that purpose or, as i explained above, a different layout where the portal is sorted by something else.

stefan