8 Replies Latest reply on Feb 18, 2015 4:17 PM by philmodjunk

    Join Table/Portal



      Join Table/Portal



      I have the following relationship:


      invoice_payment acts as a join table for payment and jobs

      I'm working on creating an interface that will allow users to pull up a list of overpayments and apply them to the current job. To do this I have a layout based on jobs, with a portal based on invoice_payment. I'd like the portal to show payments with unapplied amounts, that can be applied to the current job. I know I can filter the portal down to show only those payments, but I've realized that the portal has no records unless one is already created linking the payment with the job. Is there any way to populate the portal automatically? Or do I need to create a script to create records in invoice_payment, linking those payments and jobs together, and then delete them again if nothing is applied?

        • 1. Re: Join Table/Portal

          I'd like the portal to show payments with unapplied amounts, that can be applied to the current job

          Is there any way to link payments to jobs without the join table? I agree that you need the join table for managing payments, but if you can set up a relationship (perhaps by clientID) that shows all payments relevant to the current job, there are then a number of ways to filter that set down to just those with unapplied payments and then you can set up a portal where a mouse click can select a payment record and create a new join table record with the unapplied balance of that payment.

          • 2. Re: Join Table/Portal

            Thanks Phil,

            Seems to be working so far! I created another table occurrence of payments, linked to job by clientID, and also created another occurrence of invoice_payment, linked to job by jobID. I made the portal based on the payments table occurrence, and included a field from the invoice_payment occurrence for amount applied. 

            • 3. Re: Join Table/Portal

              Hey Phil,

              I ran into a problem with doing it this way. I have the payments table, linked to jobs by clients id, and the invoice_payments table, linked to jobs by job id. It works great except when there are multiple payments listed in the portal, with overpayments available to apply. In that case, the amount_applied field from invoice_payments shows the same amount in both portal records. Is there something I could be doing differently? Or would it be better to populate the portal using a script?


              • 4. Re: Join Table/Portal

                The records in Invoice_payments should not be showing the same value.

                If, for example, you received a $700 payment on a $500 invoice, The amount field from invoice_payments should show an amount of $500 for that invoice. The amount field from the payment table should show the full amount, $700. Once you apply the overpayment to a different invoice, you then get a second invoice_payment record with an amount of $200.

                • 5. Re: Join Table/Portal

                  Yes, that's what I'm trying to do. I think the problem is that since the amount_applied field is from invoice_payments, if I relate invoice_payments to jobs without relating it to payments, fm doesn't know which payment it belongs to. So in this case, I have a client with two payments with unapplied amounts, and in the layout to apply the overpayment, it lists both payments. One of the payments is already partially applied to the job in question, but both payment records are showing the amount applied for the one payment. Does that make sense?

                  • 6. Re: Join Table/Portal

                    But this should be a portal to payments and the Payment amount instead of Invoice_payments. A calculation field in Payments can compute the total unapplied amount and a portal filter can omit all record from the Portal Filter that have an unapplied amount of zero.

                    • 7. Re: Join Table/Portal

                      I have that part set up and working correctly, but I was trying to use an editable field based on invoice_payments so users could type in the amount to apply to a specific job. Would it be better to leave that out and script that part?

                      • 8. Re: Join Table/Portal

                        sounds like we are confusing portals here.

                        What I had in mind was one portal that listed payments with unassigned payment portions and another portal that listed payment records for a given invoice. Clicking a button in the portal row of payment could add a new record in the portal to invoice_payments with whichever value is smaller, the unpaid balance on the invoice or the unassigned payment portion from the payment record.