11 Replies Latest reply on Oct 20, 2011 4:24 PM by PedroMarroquin

    Problems with Portal with calculation fields

    PedroMarroquin

      Title

      Problems with Portal with calculation fields

      Post

      I have a portal that links # or invoice, the date in which the invoice was created, the total of that invoice, so that fields are related (from another table..). And in the same portal I made calculation fields: Paid that is a normal field and then debt which is total-paid=debt and then a status which is also a calculation field in which if debt =0 "PAID" if debt is not zero "DEBT" and I put conditional formatting so if "PAID" COLOR GREEN, if "DEBT" color red. and it works fine when i have one invoice but when i have another invoice the calculation fields that are not related and they appear automatically but the second fields do the same as the first ones... I give you the snapshot for better explaining.... PLEASE HELP! or what can I do...

      filemaker_q%27s.jpg

        • 1. Re: Problems with Portal with calculation fields
          philmodjunk

          Need a few more details:

          Are the fields shown in the portal row all defined in the same table?

          If not, how are they related?

          Is the Paid amount of $740 the correct amount for both Invoices shown? (Did the customer pay $740 twice, once for each invoice?)

          • 2. Re: Problems with Portal with calculation fields
            PedroMarroquin

            Yes the fields shown in the portal row are in the same table except the one of paid, debt and status.

            the related fields in the portal are related by the customer #

            the amount that must be paid in invoice006 is 748 and the customer paid 740 but the 740 copys into other fields of the portal! that's the problem!

            • 3. Re: Problems with Portal with calculation fields
              philmodjunk

              Please list each table involved, what fields are defined in each and by what fields they are linked in relationships.

              You'll need to change your relationships, but I need to know what you have before I can spell out the details.

              I can already see part of the problem. Since everything is matching by customer #, the payment record where the Paid field is defined links to both invoice records and that's why it appears in both portal rows and is used in both calculations.

              • 4. Re: Problems with Portal with calculation fields
                PedroMarroquin

                look the the following fields are in another table that is called "INVOICE": # of Invoice, date and total. This 3 related fields are linked by the Customer ID that appears on INVOICE and the Custumer ID that appears on the Invoice table is a related field from The Contact table so the root of the Custumer ID is in the Contacts table.

                 

                The paid field is a normal field is in the current table of the layout " Financial Archive" .

                The Debt field is a calculation in the same layout ("Financial Archive") and the calculation is this: total (related field from another table) - paid (field from current table), that is the calculation....

                 

                The the status field is a calculation field too of the same current table " Financial Archive" the calculation is: if Debt=0; "PAID","DEBT"..., so the status field is dependent of the debt field....

                The status field has conditional formatting and the calculation for that formating is this: if "PAID" = color green ; if "DEBT"= color red...

                The problem is this, with one invoice it works fine! but when i have more invoice something happens with he debt field and the paid field that messes around and thus the status field...

                 

                Is it because i'm using a portal? or is there another way to do it?

                Shall create a new field in the Financial Archive table and make a calculation that is = to the total field of the Invoice table? 

                 

                I reall thank you for helping me!

                • 5. Re: Problems with Portal with calculation fields
                  philmodjunk

                  Please Go to Manage | Database | Relationships and make a screen capture of this window. Crop it if necessary and then upload it to your next post. I'm still not clear as to what you have and that should provide the details you need to get this to work. The use of a portal is not the issue by and of itself, but rather I can tell that you haven't set up relationships and tables that will make this happen the way you want it to. By starting from that screen shot, I should be able to spell out the changes that you need to make this work.

                  • 6. Re: Problems with Portal with calculation fields
                    PedroMarroquin

                    REALLY THANK YOU! for the patience and all!

                    • 7. Re: Problems with Portal with calculation fields
                      PedroMarroquin

                      THANKS SO MUCH FOR HELPING ME AND FOR THE PATIENCE!

                      as you can see all the invoices and the financial archive are linked bt the custumer id that is in the contact table...

                      • 8. Re: Problems with Portal with calculation fields
                        philmodjunk

                        A few final details needed. (apologies for not asking for these in my last post).

                        On the layout shown in your first post, which of these names appears in "Show Records From" in Layout Setup? Contact, Proper Invoices, Invoices or Financial Archive?

                        Enter layout mode and look at the text in the bottom of the portal. Which of those names appears there?

                        Now I have questions about how you want to handle payement records.

                        Clearly a client can make a partial payment on an invoice. Can a client pay off all outsanding invoices with one payment? This sets up a many to many relationship between Invoices and Payments as a single invoice could require two or more payments and a single payment can be applied to more than one invoice. Such a situation requires a join table and relationship links bewteen it and Invoices and a Payments table (Financial Archives maybe?).

                        Will the user recording the payment information choose to what invoice to apply a given payment or should it be applied to the oldest unpaid invoice for that contact?

                        • 9. Re: Problems with Portal with calculation fields
                          PedroMarroquin

                          Here's what i want to do, i want the user to come to the layout and just choose the custumer id enter it and automatically appear all the invoices that have been done to that customer. I want the user to add partial payments to each invoice so: invoice 0001  total =300 paid 120

                           invoice007 total= 540 paid 420   invoice0012  total= 120 paid 120.... so the user just can come check the customer id and just see the status if all is paid or the custumer owes money and then the user calls the custumr and then the custumer comes and make partial payments to the debts he has....

                           

                           

                          Here's a snapshot of the portal in layout mode...

                          • 10. Re: Problems with Portal with calculation fields
                            philmodjunk

                            So what will the user do if a contact has two unpaid invoices: for $200 and $300 and sends you a check for $500? Will you enter two payments, one for each invoice even though you have received just one payment? (That works in terms of the math, but some bookkeepers and auditors get pretty upset when they see that as it makes it harder to match checks cashed to payments logged to make sure no payments were omitted.)

                            That's a business procedures issue, not a database one, so for now I'll work with what you have to suggest a fix...

                            First problem is that Financial Archives is were you record payments so the above layout looks like one that should instead be based on Contact so that you have one record for each contact and the portal will list each Invoice. I also note that your Portal Setup... lists "invoices_common" yet your screen shot does not show a table occurrence box of that name. That's impossible unless there are other parts of Manage | Database | Relationships not shown in your screen shot. There has to be an occurrence named Invoice_common or you can't select that name in the show related records from drop down here.

                            Keeping it simple and assuming that a single payment can't be logged to pay off more than one invoice...

                            Define your payment and debt field in Invoices instead of Financial Archives. Then you can subtract the payment from the invoice amount to compute the outstanding balance or "debt". A summary field in this same table can compute the total of debt to report the total owed by a given customer.

                            • 11. Re: Problems with Portal with calculation fields
                              PedroMarroquin

                              FRIEND do you have messenger? or some im client because i don't understand all of what you told me, and it would be easier to show you snaphots...