12 Replies Latest reply on Jul 6, 2009 2:17 PM by philmodjunk

    Automatically copy fields from two tables to another when New Register added

    Socio.t

      Title

      Automatically copy fields from two tables to another when New Register added

      Post

      Hello everyone,

       

      I hope you are not getting tired of me. The truth is that you are really helping me a lot.

       

      I creating these account movement database, based on debits and credits.

       

      Basically I have two tables where I insert the descript and value of credit or debit and when saving the record I would like these values to be copied automatically to a "central" table that register each movement on their respective date and then calculates initial and final value after the movement.

       

      These Credit/Debit tables are related to the Movement table through the Credit IDcode and Debit IDcode.

       

       

      I have no idea how to do this! Don't know if there are some scripts or commands to hrlp me do this. I'm kind of stuck here and really need the help of somehoe that knows what he/she is doing. :smileywink:

       

      I'm glad if someone is so kind to start a conversation with to help me solve this puzzle.

       

      Thank you very much.

       

      Best regards,

       

      Socio

        • 1. Re: Automatically copy fields from two tables to another when New Register added
          philmodjunk
            

          I sometimes set up a basic check register type layout to log debits and credits (or adding/removing inventory items):

           

          Define at least the following fields

          Transaction date (date)

          Description (text)

          Debit (number)

          Credit (number)

          BalanceCalc (Debit - Credit, returns number)

          Balance (summary, Running total of BalanceCalc)

           

          Then you place these fields on a list or table view layout:

           

          Transaction date, Description, Debit, Credit, Balance

           

          Now you have a simple register where you can enter debits, credits and the system computes and displays a running balance.

           

          This is only a starting point, but will this work as your "movement table"?

          • 2. Re: Automatically copy fields from two tables to another when New Register added
            Socio.t
              

            Hello Phil,

             

            This will work perfectly on my "Movement Table". I have got so far but I got stuck.

             

            What I don't know how to do is how to get values there.

             

            Has I wanted to explain on my previous text, I want to insert the records the other tables and want them to be copied automatically to this table as a Credit or Debit.

             

            It is like I want people to insert these records on different tables and then the system would copy them into one other different table and sort them by date and make the calculations.

             

            I know how to get the calculations done, you helped me getting that fixed. But I don't know how to get the values there. :smileysad:

             

            Well I hope there is an easy way to get this done. You should already realize by the moment I don't have much experience. :smileytongue:

             

            Thank you so much for your help.

             

            Best regards,

             

            Socio

            • 3. Re: Automatically copy fields from two tables to another when New Register added
              philmodjunk
                

              I described the “check register” method to establish a basis for a more enhanced solution. Now that I know it works for you, we can build on that to possibly produce what you want. I’ve read your earlier thread so have some idea as to what you want to do. 

               

              What's not clear to me is exactly how/why you want to "insert these records on different tables and then the system would copy them into another table (presumably the 'check register' I've described)." 

               

              What does that “insert and copy” operation accomplish for you? 

               

              The reason I ask, is that we could add one more field to the earlier example and call it "Category" or maybe "fund". Then you could enter your transactions directly into this register but use the value in "Category" to control how to group the records. Example: You have a capital expense fund for building projects. As you disburse funds to pay for a given project, you enter the transaction and lable it "Capital Exp." in this new category field. Then, when you need to know what the total spending on captial expenses is, you perform a find specifying "Captial Exp." in the category field to pull up only those transactions. The running total summary field will then give you the total spending on the last line of the register.

              • 4. Re: Automatically copy fields from two tables to another when New Register added
                Socio.t
                  

                Hello Phil,

                 

                Thank you so much for hosting this conversation with me.

                 

                What I want acomplish with the "insert and copy" operation is that values are inserted only one time.

                 

                I will have this table to insert expenses and incomes and I will have another table with different bank accounts. Each expense or income is related to a bank account number from the company department that executes the project.

                 

                When I insert an expense and set its status as approved or set the status of an invoice as payed I wish that this movement is automatically registered in the matching bank account as a credit or a debit.

                 

                This way I could have a register and control all my bank accounts movements while I am generating invoices and expenses.

                 

                I hope I'm expressing my need in a clear way. Not sure if this can be done. I thought this would be easy, but maybe I am doing the right approach

                 

                My challenge is how to get the right value in the right field and what would be the best way to have these tables related without generating this monster relationship chart.

                 

                Once again thank you very much for your help.

                 

                Best regards,

                 

                Socio

                • 5. Re: Automatically copy fields from two tables to another when New Register added
                  philmodjunk
                    

                  I believe I understand what you want, but am not convinced you need to enter the data in one table and have the system copy it to another. It looks to me that you can get what you need simply by managing the found set of your "register" set up we've already discussed.

                   

                  Think about this model and see if it works for you:

                  Add a field to the "register" table and call it AcctNo. Create a second table and call it Accounts. You may define as many fields as you need in the accounts table in order for one record to identify/describe one account. Put a matching AcctNo field in this table and set a validation to insure that the value in this field is unique. Use your relationship graph to create the following relationship:   Register::AcctNo--=--Accounts::AcctNo.

                   

                  Now you can enter all your transactions directly into the register table, but use the records in the Accounts table to refer to just those transactions that belong to a given account.


                  Here are two ways to make that happen.

                  1) Create a layout based on the Accounts table. Place a portal that lists the necessary register fields from the register table. Enable the "All creation of records via this relationship option for the above relationship." Now you can select an account record and be able to view/edit the transactions for that account.

                   

                  2) Take your list layout we created earlier and add whatever fields are relevant from the Accounts table to its header. Add the account number field to the body--as the leftmost field will probably make the most sense. Format this field as a drop down menu that takes its values from the Accounts::AcctNo field.

                   

                  To work with all the transactions for a given account, perform a find to find all transactions with the same account number. This process can be scripted. If you are using Filemaker 10, You can set even set up a drop down menu that enables you to select a given account and then automatically find and sort the specified transaction records.

                   

                  Let me know if either or both of those methods (they're not exclusive) will work for you and then we can go into more detail.

                  • 6. Re: Automatically copy fields from two tables to another when New Register added
                    Socio.t
                      

                    Hey Phil,

                     

                    Almost got it. I think this usage of the portal to get the list of transactions can be a really good thing.

                     

                    The challenge I am having at the moment is that somehow it doesn't recognize the the account that don't start woth a double zero. Like "0020" or "0081", if I introduce the "3181" it wont list the transactions. :smileysurprised: But this shouln't be a problem. As I don't want users to see the entire account number as they insert the records I can use an alias and maybe it works tha way.

                     

                    Since we are on this issue I have another doubt.

                     

                    Is there a way to generate transactions between accounts. I mean, generally with this structute I would make a credit transction from one account and a debit in the other one.

                    But do you think we can automate this operation, saying that if we can instruct him somehow to get a certain amount from one account to another account he generates the credits and debits automatically?

                     

                    I believe that the we would need to let the system know in some way that the money is getting out of an account but its getting into another account.

                     

                    Thank you very much. I believe I have here a pretty complex data base but I'm taking it step by step.

                     

                    Best regards,

                     

                    Socio

                    • 7. Re: Automatically copy fields from two tables to another when New Register added
                      philmodjunk
                        

                      "somehow it doesn't recognize the the account that don't start woth a double zero. Like "0020" or "0081", "

                      Make your account number field in both tables text fields, you can convert them without losing any current data just by opening up the field definitions and select "Text" in place of "number". In fields where you need your leading zeroes, you'll need to find and edit any existing records by typing in the missing zeroes.

                       

                      "Is there a way to generate transactions between accounts. I mean, generally with this structute I would make a credit transction from one account and a debit in the other one."

                      Easily done with a script. Have you created scripts before? If not you'll will need to learn how in order to take full advantage of Filemaker's capabilities.

                       

                      Define a new global text field: "gTransAccount" and place it in the header of your layout, put a button in the body of your register layout named "Transfer To" or some such,  and attach it to the following script:

                      If [Register::Credit > 0 and Not IsEmpty(gTransAccount)]

                        Duplicate Record/Request

                        Set Field [register::AcctNo; gTransAccount]

                        Set Field [register::Credit; Register:: Debit]

                        Set Field [Register:: Debit; ""]

                      End If

                       

                      This duplicates the current record if it's a debit entry and you've entered a target account, labels it with the account number you entered in gTransAccount, then copies the debit amount to the credit field and clears the copied debit amount.

                       

                      Note: I may have used table and field names that differ from your file, check to make sure you use tablename::fieldnames that match your actual names in your file.

                       

                      • 8. Re: Automatically copy fields from two tables to another when New Register added
                        Socio.t
                          

                        Hello Phil,

                         

                        Although I don't have much experience with scripting I believe I can manage simple scripts. Already done it before.

                         

                        I'll try your tips and get back to you during the weekend.

                         

                        Thank you very much.

                         

                        Best regards,

                         

                        Socio

                        • 9. Re: Automatically copy fields from two tables to another when New Register added
                          Socio.t
                            

                          Hello Phil,

                           

                          Thank you very much for your help. I'm sorry I couldn't check on your tip at the weekend.

                           

                          I was just checking it and I think that there was some kind of missunderstanding.

                           

                          Although what you are suggesting is very handy for some situations I think that what I need is something much more simple.

                           

                          Let me try to clarify it:

                          What I need is a way to transfer a set ammount of money directly from one of my accounts to another. Imagine I have a Current Account and a Loan Account and at a certain momment my Current Account does not have the funds for something. I just transfer the funds from the Loan Account and then I can make register what I need.

                           

                          What you were suggesting I think is ti change the acounts of the registers I already have. Which is very handy for occasions that I use one account to pay something and then need to refund that account and pass the expenses to the correct account.

                           

                          Hope that this can put us back to the right track. :smileywink:

                           

                          Meanwhile I think I can introduce you to another topic which is related. I think it is very easy but with the structure I have I can't get it to work.

                           

                          This is the relationship treeof my DB (I'll try to post an printscreen):

                          http://www.angelfire.com/empire/socio/Images/Imagen_1.png

                           

                          With this structure how can I get the to see the account balance and the total by account? I can't get it right.

                           

                          Please let me know if you think of something.

                           

                          Best regards,

                           

                          Luís

                          • 10. Re: Automatically copy fields from two tables to another when New Register added
                            philmodjunk
                              

                            I can't see your posted Relationship Graph so I can't help you much there.

                             

                            "What I need is a way to transfer a set ammount of money directly from one of my accounts to another. Imagine I have a Current Account and a Loan Account and at a certain momment my Current Account does not have the funds for something. I just transfer the funds from the Loan Account and then I can make register what I need.

                             

                            What you were suggesting I think is ti change the acounts of the registers I already have. Which is very handy for occasions that I use one account to pay something and then need to refund that account and pass the expenses to the correct account."

                            In terms of the basic mechanics, I don't see the difference between your two scenarios.

                             

                            If you plan to transfer funds from your Loan account, you need to post a credit to log the disbursement of funds. If you plan to transfer them to your current account, you need to post a matching debit to that account to complete the transfer. The method I described, does exactly that. It takes the current credit, allows you to specify a different account to receive the transfer and logs a matching debit in that account.

                             

                            Can you explain the difference?

                            • 11. Re: Automatically copy fields from two tables to another when New Register added
                              Socio.t
                                

                              Hello Phil,

                               

                              I apologize for the last e-mail. I think I missinterpreted your idea.

                               

                              I just tested again your e-mail and it worked perfectly for what I want, just need to duplicate other fields like Transfer detail and I think it will work very well. Can I had SetField commands to your script? It will work like that?

                               

                              I have some doubts about this Layout. It is the same layout we talked before, I just needed to add the "Destination Account" field (I called it like this to make it easier) and the place it the header of the layout and then add a button at the body of the layout. Is that correct?

                               

                              Has I told you before I need to have the bank account balances made. But I have the structure a little more complicated then what we started.

                              I can't insert the picture of my relationship tree here. Please try to see it here: http://www.angelfire.com/empire/socio/Images/Imagen_1.png

                               

                              The fields I would like to have are:

                               

                              Initial balance: Account balance before transaction

                              Final balance: Account balance after transaction

                              Current amount: Bank account current amount

                               

                              I hope you can see the relationship tree and I would thank you very much if you could advice me on where to introduce them or how to calculate them.

                               

                              Thank you very much for your help and once again I apologize for the missunderstanding.

                               

                              Best regards,

                               

                              Luís

                              • 12. Re: Automatically copy fields from two tables to another when New Register added
                                philmodjunk
                                  

                                "I have some doubts about this Layout. It is the same layout we talked before, I just needed to add the "Destination Account" field (I called it like this to make it easier) and the place it the header of the layout and then add a button at the body of the layout. Is that correct?"

                                This method works for FMP 10. It doesn't work in FMP 5.5 (the version I used to use.) I don't know which version first made it possible to edit a global field when it is placed in the header, so if you are using an older version, you'll need to test and see if you can edit it. You want to place the button in the body, because clicking it both triggers the script and selects a specific record as the first half of the account transfer pair of transaction.

                                 

                                The fields I would like to have are:

                                 

                                Initial balance: Account balance before transaction

                                Final balance: Account balance after transaction

                                Current amount: Bank account current amount

                                 

                                You actually have each of these values already, though you may need to do a bit more work to get them where you want them.

                                 

                                Initial balance before transaction, is the running balance total on the previous line of your "ledger".

                                Account Balance after transaction is the running total for the current record.

                                current amount: this is the running total of the last line of your ledger--unless you only want the total of all transactions that have "cleared".