8 Replies Latest reply on Mar 26, 2009 1:27 PM by philmodjunk

    I need help filtering transactions, please.

    bwbono

      Title

      I need help filtering transactions, please.

      Post

      Hi all

       

      I have a school that takes monthly payments from students. These payments are either direct deposit through the bank or CASH.

      I want to separate the CASH payments and route them to another database called CASHBOOK.

       

      I just cannot seem to figure this out. Could someone give me a hand, please? Much appreciated. 

       

       

        • 1. Re: I need help filtering transactions, please.
          obeechi
            

          Just thinking out loud here. 

           

          School has many payments

          Student has many payments

          Join table between the two: payments table

           

          all check payments and cash payments are in this table.

           

          then two more table occurrences, each based on the payments table,

          where there is a relationship between payments and paymentsCash, and

          where there is a relationship between payments and paymentsCheck

           

          then a portal, or list view, which shows only paymentsCash, and

          then a portal, or list view, which shows only paymentsCheck

           

          the portals are on layouts which are based a parent table of the payments records, like student, or school

           

          you relationship would be between typeOfPaymentField in one table occurrence, and typeOfPaymentField in another table occurrence 

          this is the same field in the underlying table

          a second criteria can be added to show only the same students records (or only the same school)

           

          do you need a global for this? haven't thought that one through, still wrapping filemaker in my mind. I'll let someone else answer that.  

          • 2. Re: I need help filtering transactions, please.
            philmodjunk
              

            bwbono wrote:

            Hi all

             

            I have a school that takes monthly payments from students. These payments are either direct deposit through the bank or CASH.

            I want to separate the CASH payments and route them to another database called CASHBOOK.

             

            I just cannot seem to figure this out. Could someone give me a hand, please? Much appreciated. 

             

             


             

            The key verb here is "route". Do you need to physically copy the data to another FMP database? That can be easily done.

             

            Follow up question: assuming you want to physically copy the data, why?

             

            If you can answer these questions, we should be better able to answer your questions.


            • 3. Re: I need help filtering transactions, please.
              bwbono
                

              Thank you for responding.

               

              I do not need to copy the data. I would just like the CASH payments to then be entered into a CASHBOOK as a DEPOSIT, so I am able to track and report on daily cash transactions. 

              Example: A student wants to pay tuition payment and has half in CASH and the other half is directly deposited from their bank into mine. After I record this in Filemaker, I need to take this cash portion and enter it then into a separate database..CASHBOOK...so I can then monitor cash transactions for  daily uses.

               

              Thanks again for spending time thinking about this. 

              • 4. Re: I need help filtering transactions, please.
                philmodjunk
                  

                 

                "I do not need to copy the data. "

                "I would just like the CASH payments to then be entered into a CASHBOOK as a DEPOSIT."

                "I need to take this cash portion and enter it then into a separate database..CASHBOOK..."

                 

                What is CASHBOOK? Is it a 2nd filemaker database? A second table in the same database? A completely different application?

                 

                I'm just trying to be clear as to what you want to do.

                • 5. Re: I need help filtering transactions, please.
                  bwbono
                    

                  CASHBOOK is a second Filemaker database to keep track of only cash transactions.

                   

                  My idea is that a student comes in to pay her monthly tuition payment with half cash and the other half direct bank deposit/transfer.

                   

                  I just need a way to keep track of the cash that passes through, so I created a simple database I call CASHBOOK that is basically like a checkbook register...recording tranactions.

                   

                  After this tuition payment transaction with this student is complete, I now have actual cash to deal with and account for. I would like this database I call TRANSACTIONS (where these tuition transactions take place) to take only the cash portion of her tuition payment and then "deposit" this amount into this CASHBOOK database. For various reasons, I need to track this cash.

                   

                  I hope this clears up my thought process. Thanks again for helping me hone in on my problem. 

                   

                  • 6. Re: I need help filtering transactions, please.
                    philmodjunk
                      

                    I've done a "quicken" type check book register/budget manager in FMP before.

                     

                    Answer 1:

                     

                    Yes you can move data from one filemaker file to another. you can use Import Records to do this. This process can copy data from as many records are in your found set from one file append it ot a table in another. This can be done manually or in a script. If you only have data from one record to send at a time manipulate your found set so that it is the only record in your found set. If you are absolutely sure that this is what you want to do, I can elaborate in my next post.

                     

                    Answer 2:

                     

                    Do you have two files or one file with two tables? It's much better database design to define separate tables in the same file than to have separate files. In this case you can write a script and use the set field instruction to move data from table to table.

                     

                    Answer 3: 

                    I'm unconvinced that this is really what you should do. Just going from what you described so far, I'd put a field in my transactions table called TransactionType. I'd then put "Cash" in this field for cash deposits and different text in other transactions. Then, any time I want to see just cash transactions, I perform a find in this field looking for "Cash".

                    • 7. Re: I need help filtering transactions, please.
                      bwbono
                        

                      I admit my design is sprawling. I do use separate databases....not tables.

                      For even this small school, the database becomes enormous quickly, so I decided to break it apart into manageable databases.

                       

                      I am building a complete solution to cover every facet of the school. 

                       

                      I also admit I am "poking around" until I get the results I try for...I am not so talented with database thinking and design, but I am persistent!  

                       

                      I am certain another would see my "solutions" and immediately see a easier, clearer way to accomplish the same thing. As I build, I find myself doing this more and more. Filemaker is absolutely great. I just am too fuzzy headed to connect all the dots.

                       

                      So in this case, I try to complete a tuition transaction in one database called TRANSACTIONS and at the same time, create a new record (transaction) in another database called CASHBOOK of only the cash portion of this payment.

                       

                      The CASHBOOK then is used entirely separate from the rest of the school solution for daily uses...like making bank deposits, buying supplies, etc. 

                       I hope this helps.

                       

                      Thank you again. 

                       

                       

                      • 8. Re: I need help filtering transactions, please.
                        philmodjunk
                          

                        Then Import Records will do the job. There are many feature/options to this tool, so I recommend reading up on it in the help file and experimenting with it on copies of your databases until you are confident that it will work for you.

                         

                        A general description of the process:

                         

                        In cashbook, create a script that uses import records to copy the data from Transactions into Cashbook.

                         

                        In Transactions, create a script that checks to make sure that no required fields are blank, sets up the found set required and uses Perform External Script to trigger the import script in cashbook.

                         

                        You then need to figure out how this second script will be triggered. The traditional approach is to place a button on your layout. If you use FMP 10, you also have the option of using a script trigger. The best approach is up to you as it depends a lot on how you've set this up.

                         

                        If you know that you're always going to just post data from the current record to CASHBOOK...

                         

                        Find all records

                        Omit records

                        Show all omitted

                         

                        Is a quick way to isolate the current record in a found set of just one record.