10 Replies Latest reply on Sep 11, 2014 8:42 AM by philmodjunk

    Filter Portal with Portal

    Gilo

      Title

      Filter Portal with Portal

      Post

      Hi 

      I have 2 tables, one with payments due and one with payments paid. I have a script compare both, which works ok. 

      Unfortunately the script makes some mistakes due to the incomprehensiveness of the file. So I need to compare some records on my own.

      My question is: Is it possible to have a list of payments due. And if i click on a payment in the due list, filemaker shows me all the records from paid which correspond to the due record? 

       

      Thx

        • 1. Re: Filter Portal with Portal
          philmodjunk

          It sounds quite possible, but what data in the payment due table allows you to identify which payments paid records correspond to it?

          What table is your layout based on and how have you set up the relationships linking that table to the two tables you have described in your post?

          • 2. Re: Filter Portal with Portal
            Gilo

            Thax Phil for you reply.

            Right now I have two distinct tables, "due" and "paid" 

            In every table i have client_num,  contract_num, client_name, client_adress, price.

            My script compares the two tables and if there is a match, fm sets the field "Status" to ok which means, that the payement due is paid.

            I have a 3rd table, where i wanted to put the menu on but it's not used, because i don't know how to get started. 

            • 3. Re: Filter Portal with Portal
              Gilo

              I would need something similar to this, where the top table shows the table_due and the bottom should show the data from table_paid, but only the ones corresponding to the line 1 clicked on the top table. Is this possible?

               

              • 4. Re: Filter Portal with Portal
                philmodjunk

                Unfortunately, the image you've uploaded is too small for me to see any meaningful detail.

                To repeat the question: "but what data in the payment due table allows you to identify which payments paid records correspond to it?"

                Would a "match" be the same client_num and contract_num values?

                And on what table is your layout based on? A table of clients? or a table of contracts? or ???

                • 5. Re: Filter Portal with Portal
                  Gilo

                  Sorry for the messing up, the image was an idea on how it should look like. With two tables on one layout.

                  due table has the following fields: client_no, contract_no, client_name, price

                  paid table has the following fields: client_no, contract_no, client_name, price

                   

                  If due:client_no = paid:client_no AND due: contract_no = paid:contract_no AND due:client_name = paid:client_name AND due:price = paid:price then it's a match

                  The DB is meant to check if the bills (due) are paid. 

                   

                  For easiness reasons on the compare by the user i wanted to be able to see all my due date (maybe even be able to filter that data) and if I click on a record on due, he shows me the matches of paid in another table. I don't know on what data the layout should be based, with my novice knowledge I would guess that it has to base on a third table, because i want the due data to be filtered also. Without filtering I would guess it should base on due table. 

                  • 6. Re: Filter Portal with Portal
                    philmodjunk

                    Neither table should have a client name field. That's redundant data that should only be stored in the client table. (imagine what you'll need to do if a client changes their name or you discover after the fact that you've entered their name with incorrect spelling...)

                    If due:client_no = paid:client_no AND due: contract_no = paid:contract_no AND due:client_name = paid:client_name AND due:price = paid:price then it's a match

                    If the client_no matches, there should be no need to compare client names.

                    And why do you need to match by price? (my concern here is that if you have multiple billings on the same contract, all of the above criteria might match values but NOT be two records that should match to tell you if the bill was paid.)

                    I think a different data model where you link a bill to the payments that apply to it by a Billing ID number would make it much easier to link a particular bill to all payments that might be linked to it. (and there's a way to do this even if a single payment is used by your client to pay off multiple bills over multiple contracts...)

                    • 7. Re: Filter Portal with Portal
                      Gilo

                      The Price is needed because a client might pay twice for one bill, as some bills are due monthly. So if a customer forgets one month, he might pay the next month twice. 

                      I know that some data is redundant. But my first concern was, not to miss any data, so i prefered to check twice instead of missing one... 

                      Do you have a template or tutorial for your data model? 

                       

                      One of my problems is that my data come from two files put it place by the company. So I don't have too much impact on that data. But I'm open to learn more about your data model

                      • 8. Re: Filter Portal with Portal
                        philmodjunk

                        Here's a typical invoicing data model. It's the same structure but with different names that you'll find invoice and purchase order starter solutions that have come out with the different versions of FileMaker:

                        Customer-----<Invoices-------<LineItems>---------Products (----< means "one to many")

                        To add in a system for processing all kinds of payments, while linking each payment to one or more invoices so that we can tell which invoices are paid in full and which have a balance due you add these table occurrences:

                        Invoices|payment------<Payment_Invoice>------Payments>------Customer------<Invoices|Due

                        Each time you receive a payment, you create a new record in Payments linked to the correct Customer record. The customer to Invoices|Due relationship would be used to get a list of all unpaid invoices for that customer. You'd then apply that payment to one or more invoices by creating a record in Payment_invoice that links to Invoices|payment by InvoiceID and to payments by paymentID. A number field in Payment_Invoice records the portion of the Payment that applies to the linked Invoice. When the sum of this field across all linked Payment_Invoice records equals the total of the invoice, the invoice is paid in full.

                        Scripts can be used to automate this process, such as taking the total of the new payment, and start with the oldest Invoice due and pay of each in turn until the entire payment is accounted for.

                        This system can handle partial payments, payments that pay off multiple invoices and payments that pay off several invoices in full and another partially. You can manually assign invoices to the payment or use a script.

                        • 9. Re: Filter Portal with Portal
                          Gilo

                          Thank you Phil

                          But this is a DB where I manage the paiments and invoices. But in my case, the company takes car of paiments and invoices and gives us (we're an agency of the company) 2 excel files, one with invoices and one with paiments. 

                          What I would need, is a solution to compare the two files. I got that to work. 

                          Unfortunately, the company's excel is not always very clear, so if there are paiments from the month before we have to check by hand. 

                           

                          So to check by hand, I would need a possibility to view the due records. And for convenence reasons it would be great to have a 2nd portal on the same layout to show payments. Perfect would be, if I could click on a record in due and paiments would show the records corresponding to client_no