10 Replies Latest reply on Mar 28, 2012 9:13 AM by keith

    Portal Query

    keith

      Title

      Portal Query

      Post

      FM11 Mac 10.6

      Investments Table: Around 20 Records. Statements Table: Normal Bank Statements.

      Investments - Statements Relationship based on Investment ID. Tiny Portal (same size as field) on Investments layout shows the latest Statement balance for the particular Investment.

      However, i normally view Investments via a Portal in my Contacts file which doubles as a 'Home' layout.

      My question is: How do I also show the Statement Balance on the Investments Portal on the Contacts layout?

      Thanks

      Keith

        • 1. Re: Portal Query
          philmodjunk

          You have this relationship:

          Investments::InvestmentID = Statements::InvestmentID

          and probably this one:

          contacts::ContactID = Investments::contactID

          How do you compute the latest statement balance in your portal row?

          If it's a summary field, you may be able to define a calculation field in Investments that uses Sum to compute the same total, but I'd need more details on how you compute the value you are currently using a portal to display. Might it be a filtered portal? if so what is the filter?

          • 2. Re: Portal Query
            keith

            Correct on Relationships. Filter in use in Contacts but only to give me other options. No filter in Investments - Statements.

            Nothing clever on Portal in Investments for Balance. Portal merely sorts Date Descending and therefore shows most recent balance on first line of Portal.

            Hope I've made it clear.

            Thanks

            Keith

            • 3. Re: Portal Query
              philmodjunk

              That tells me how you show the balance, not how you compute it, but it should be enough here.

              See if this calculation field works when defined in Investments:

              Last ( Statements::Balance )

              • 4. Re: Portal Query
                keith

                Sorry if I'm confusing you but there is nothing to calculate. Every Statement record has a Balance and I just take the first (top of list of records based on date).

                As I say, no problem showing Balance in Investments but haven't a clue how I show it on Investments Portal in Contacts.

                Tried    Last ( Statements::Balance )    which works except it isn't giving most recent Balance. seems random, i.e. balance a few weeks to a few months old.

                Thanks

                Keith

                • 5. Re: Portal Query
                  philmodjunk

                  I assumed that the most recent balance would be the last related record--which is what last returns save that it actually returns the last related record for which the value is not empty.

                  What sorting have you specified for your portal? Is this specified in portal setup or in the relationship?

                  If the sorting is set up to show the first related record and the sort order is specified for the relationship, then a calculation field that is simply written as:

                  Statements::Balance will return the "first" related record. Which record is "first" will be determined by the sort order specified for the relationship. If there is no sort order specified, it returns the first related record to be created.

                  To specify a sort order in a relationship, open Manage | Database | Relationships and double click the relationship line linking the two table occurrences. Then you'll find a sort check box to click that opens the Sort Records dialog where you can specify a sort order. Changing/specifying the sort order at the relationship level can affect what value is returned by Last () and by the plain reference as well as the order of records shown in Unsorted portals. It also affects the order of records pulled up by a go to related records script step.

                  • 6. Re: Portal Query
                    keith

                    My tiny Portal in Investments uses Portal sorting - I have never used Relationship sorting.

                    When I sort the Statement records, I noticed that my highest ID number was not the first record but was around 4th in the order. I assume the Last Calc is choosing the record based on the highest ID. I don't know why my sorted order does not match the IDs but it is not a major problem.

                    However, when I was looking at the Statements file, something struck me which I had forgotten about. I will explain but I need to give you an idea of how my Statements file looks. 

                    In Table View, there is a record for each Bank Statement line with fields for Date, Details Db, Cr and Balance. There is a header record for each investment which describes the investment. I also have various analysis fields but in particular there is a Bal L field (Liquid, cash etc.) and a Bal F field (Fixed, property etc.). These two fields are next to the main Balance field. At month end I drag the latest balance for each investment to the Bal L or Bal F field. I then finish up with let's say 20 entries in the Bal L column and 5 entries in the Bal F column. There are Bal L and Bal F summary fields which give the Total Liquid and Fixed investments.

                    I am now thinking, why not use the Bal L and Bal F fields instead of the Balance field as there is only one balance for each investment? I tried this by applying the Last Calc to the Bal L field and had a pleasant surprise as the Bal L balance was returned to the Investments file irrespective of which Statements record it was applied to. It seems that FM finds the balance if the other records have no Bal L entry. To check this, I entered different Bal L balances in the first 5 or so records of an inveestment and FM then chose the 4th record - similar to the original problem as mentioned in my 2nd para above.

                    I know I'm being greedy but is there any way the Bal L and Bal F fields could be calcs to return the latest balance (just once for each investment) instead of my dragging each month? It would be simple for me to add a field to show if it is an L or F investment.

                    What do you think?

                    Thanks

                    Keith

                    • 7. Re: Portal Query
                      philmodjunk

                      To compute a running balance, I'd use either a summary field or a self join relationship to access compute the current balance. Which is best depends on how you set up your database.

                      I usually set up a "ledger" type table like this:

                      TransactionDate (date)
                      Description (text)
                      AccountID (ID foreignkey to accounts table)
                      Debit (Number)
                      Credit (number)
                      cBal (calculation: Debit - Credit )
                      sBalanceRunning (Summary field, running total of cBal)
                      sBalance (Summary field, total fo cbal)

                      In a table with transactions with multiple accounts, I sort the records first by Account, then by transactionDate and specify AccountID as the groupBy field in the setup for sBalRunning. I then set up the list or table view of the records to include the TranasactionDate, Description, Debit, Credit and sBalRunning field.

                      sBalance can be used from a layout based on Accounts to display the current balance on the account to produce a layout where you have one line showing the account info and current balance.

                      That may give you some ideas on how to set up your table.

                      • 8. Re: Portal Query
                        keith

                        Ah. In the past, I have considered changing and doing it properly with Calc and Sum fields etc.

                        At the moment all accounts are retained as individual OpenOffice spreadsheets and vary from monthly Bank Statement downloads to ones which are updated manually. When all accounts are complete for the month, they are imported into FM.

                        I have hesitated to change my FM file as I would have to apply double entry rules, i.e. I 'cheat' at the moment. For example, for some accounts, I enter a 'valuation' balance each month but don't bother with a Db or Cr entry. I would also have to add records to show a B/F figure for older accounts which commenced before the first FM record. There are probably one or two other things to tidy up so that a Balance Calc would be correct. Apart from the initial setting up, I may actually finish up doing more work every month. On the other hand, I can see that there would be a plus or two in changing. 

                        Nevertheless, I'm not sure that it is worthwhile as my only problem is collecting the account balances each month. Pity there isn't another way to collect these. 

                        However, following your post, I have created one or two calc fields and am having a dabble. Your comments certainly give me some direction and food for thought.

                        Thanks

                        Keith

                        • 9. Re: Portal Query
                          philmodjunk

                          Yes. I didn't think you'd just copy the method verbatum, but hoped that as an example of how calculation and summary fields can be used in this fashion, it might spark some ideas you could adapt to your existing setup.

                          • 10. Re: Portal Query
                            keith

                            Apppreciate your comments. Problem is my DB and imagination!

                            Thanks again.

                            Keith