13 Replies Latest reply on Jun 13, 2014 10:27 AM by barcello

    Uploading the latest date from related table

    barcello

      Hi everybody,

      I have two tables:

      • checking account
      • bank balance.

      In the first one I stored all the details of my company’s accounts (including the balance using a relationship) and in the second I store the monthly balance (every end of the month).

      Now, I would like to show in the report, based on the checking account table, always the latest bank balance from the second table. It means that every time I open the report I need to see, within all my info also the last month balance.

       

      Thank you

        • 1. Re: Uploading the latest date from related table
          erolst

          barcello wrote:

          the latest bank balance from the second table. It means that every time I open the report I need to see, within all my info also the last month balance.

           

          Wouldn't that simply be the latest existing entry? In that case, just use a relationship or a portal sorted by date descending (or by year and month, depending on how you store the year/month value).

           

          With a relationship, you can simply display the related fields; with a portal, set it to display one row only.

          • 2. Re: Uploading the latest date from related table
            barcello

            The point is that I have 4 checking accounts and so every month a store in the bank balance table 4 balances (each for every account related by an id_account) with the date of the balance in e second field.

            So, when I open my report I only want to see the last balances, without the older ones.

             

            Much better would be if I could choose a month, from a drop down field in my report, and see the montly balances of my 4 checking accounts!

            • 3. Re: Uploading the latest date from related table
              erolst

              Well, then from your report context, either define a filtered relationship into a BankBalance TO, or a cartesian relationship (connect any two fields via the 'x' operator) with a filtered portal.

               

              Cannot exactly tell you how the relationship needs to be set up or what the filter calculation would look like, since I don't know how you denote the year/month in the BankBalance table (as a date, or using number fields for month and year).

               

              barcello wrote:

              Much better would be if I could choose a month, from a drop down field in my report, and see the montly balances of my 4 checking accounts!

               

              That's a new one – this post started out with just "last month” …

               

              I suggest you either post your file (in a sanitized version, or as a clone with a few sample records), or provide more details about your fields.

              • 4. Re: Uploading the latest date from related table
                PSI

                This is a perfect us of ExecuteSQL()

                 

                I do this to display Last Order Date, amount of last order amonut year to date. I put regular fields in the Client file then use a script triggger ( onRecordLoad) to fire off the script so its always updated.

                 

                You could use a let statement to capture the Last Date, combined with the account # then grab the latest balance.

                 

                John

                • 5. Re: Uploading the latest date from related table
                  barcello

                  Thanks' PSI but.... I'm afraid this is too much for me...! I don't have such an experience in FM to follow your solutions….! sorry

                  • 6. Re: Uploading the latest date from related table
                    pmconaway

                    Barcello, have you done any filemaker scripting? What level of experience do you have? What PSI is suggesting isn't terribly complicated with a little experience. i.e. more complicated uses of script triggers or the ExecuteSQL function.

                    • 7. Re: Uploading the latest date from related table
                      barcello

                      Hi pmconaway, I have same kind of basic experience and I already have done scripting, but I'm trying to make a script on what PSI suggested me... need to work on it!!..

                      • 8. Re: Uploading the latest date from related table
                        erolst

                        pmconaway wrote:

                         

                        Barcello, have you done any filemaker scripting? What level of experience do you have? What PSI is suggesting isn't terribly complicated with a little experience.

                         

                        It is not complicated, but IMO is complicating things unnecessarily, when a simple relationship or filtered portal does the job. It would be better to get a grip of FM basics before trying the more esoteric.

                        • 9. Re: Uploading the latest date from related table
                          barcello

                          I’ll try to give more information:

                          I have four tables

                          • Checking_account
                          • Bank_balance
                          • Company
                          • Bank

                          In the main report I have a portal in which, selecting the different company, I can see the account’s details such as the account number, the rate, the bank and the balance.

                          Now, every last day of the month, I update the balance of every account (in the bank_balance table) and, what I would like to see, is my latest balance in the portal together  with all the other “static” information.

                          I can even think of inserting a date field in the main report, so that the script (I imagine) can calculate the closest  month’s last day (my be this is too much, I would be satisfied juas if I could see my latest bank balance…).

                          The format date in the bank_balance table is MM/DD/YY.

                           

                          pic1.png

                          • 10. Re: Uploading the latest date from related table
                            barcello

                            Anyone can help me.....

                            • 11. Re: Uploading the latest date from related table
                              DanielShanahan

                              Add a date field to the bank_balance table.  You don't need a portal if you just want to view one record in the related table.  Just put the  bank_balance::date and bank_balance::balance on the layout (report, form view, list view, etc.).

                               

                              Use a Cartesian join (the "X") to show everything and in the relationship dialog (i.e. where you set the Cartesian join), sort by bank_balance::date descending.

                              • 12. Re: Uploading the latest date from related table
                                erolst

                                Why don't you have a look at this little sample. It creates and updates monthly balances as you go – the only thing left to do is to add a routine that on deletion of the only entry for a month deletes the associated monthly balance record and updates all following months.

                                 

                                This means that you only need to add (or delete) transactions, and the script manages the monthly balances. No summaries and no unstored calculations, plus performing only the necessary recalculations, should make for nice response times.

                                 

                                Oh … and it shows you the balances of the recent month for all accounts!

                                • 13. Re: Uploading the latest date from related table
                                  barcello

                                  Thanks' erolst,

                                  I've got all the week end long to find out the solution..... I hope