7 Replies Latest reply on Jul 13, 2010 7:32 PM by eibcga

    Filter portal records

    eibcga

      Title

      Filter portal records

      Post

      In the ACCOUNTS layout, I added a portal to show specified fields for all related records from LEDGER and JOURNAL.  I would also like to be able to input the date ranges.  I created two global date fields in ACCOUNTS called g_start_date and g_end_date.

      In portal setup, I specified the following Boolean (True/False) formula in the filter portal records:

      If ( JOURNAL::date>ACCOUNTS::g_start_date ; 1 ; 0 )

      This gives me all records after the start date I specified — good.  But, when I change the start date, the records do not dynamically change — not so good.

      I went a step further and replaced the above formula to specify a date range.  This formula was accepted, but the result did not work at all (the portal showed all related records, not just the records from the start date).

      Case ( JOURNAL::date ≥ ACCOUNTS::g_start_date ; 1 ; JOURNAL::date ≤ ACCOUNTS::g_end_date ; 1 ; 0 )

      I'm missing something but I don't know what.  I watched related training videos on the subject and did some searching, but no luck.

      Thank you, from a beginner on FMP11 on Mac OS X 10.6.4.  Thanks for baring with my questions, I'm learning a lot since joining FM a month ago.

      Screen_shot_2010-07-08_at_11.55.35_PM.png

        • 1. Re: Filter portal records
          LaRetta_1

          Case ( JOURNAL::date ≥ ACCOUNTS::g_start_date ; 1 ; JOURNAL::date ≤ ACCOUNTS::g_end_date ; 1 ; 0 )

          can simply be:

          JOURNAL::date ≥ ACCOUNTS::g_start_date  and JOURNAL::date ≤ ACCOUNTS::g_end_date

          You will need a winow Refresh for it to change (include flush cache join results).

          • 2. Re: Filter portal records
            eibcga

            Thanks LaRetta,

            Your last comment eluded to some more intermediate things I had to get my head around and I realized I needed a Script Trigger so the window would refresh after I input in start and end dates.  To confuse things further, the FMP11 Help documentation for filtering records in a portal showed an example using an IF formula, but I like your way better.  Thanks again.

            • 3. Re: Filter portal records
              eibcga

              Question: Since learning filter portal records using script triggers above, I was wondering how I could accomplish the same result by using filter relationships instead (as was done prior to FMP11 when the filter portal feature was not available)?

              I added two new table occurances, one for ACCOUNTS and one for LEDGER and copied the same relationships between them and JOURNAL.  I then wanted to edit the relationship between "ACCOUNTS 2" and "LEDGER 2" to add the two other necessary relationship criteria to consider the date ranges in the global date fields compared to the journal date field in the JOURNAL table, but the JOURNAL table is "two hops away"!  Do I need to add a calculated date field to "LEDGER 2" with the formula, "JOURNAL::date"?  Thanks in advance.

              • 4. Re: Filter portal records
                LaRetta_1

                I confess that I didn't view your graph only your request to understand why your filtered portal wouldn't work.  I've since reviewed your graph and I do not understand your accounting structure.  Your Journal has no Amount or an Account/sub-account.  Your Ledger should only be a rearrangement of those detail journal entries and can be automated.

                I will need to assume that you are using a hybrid system and I hope that you have consulted with an Accountant on setting it up or, more likely, that you aren't done structuring it yet. But let me skip to what I think is the issue:  With your current structure, you need the journal date in your Ledger so you can summarize by Account/sub-account.

                "Do I need to add a calculated date field to "LEDGER 2" with the formula, "JOURNAL::date"?"

                If you plan to filter via a relationship then yes (and if you REALLY need a second table occurrence group which I'm unsure you need unless you are designing anchor/buoy), you will need the date in your Ledger (which should have the Journal date anyway as an auto-enter or lookup from your Journal entry).  You cannot use a calculated date in Ledger for your journal date because it would be unstored and thus not available for the child side when viewing your accounts.

                Regardless how you structure this, your Journal needs an Amount and your Ledger needs the Journal Date as stored (standard data).  I do not know your reporting needs but I would assume you need a Trial Balance and that should be generated from your Ledger.

                So to cut to the chase, add JournalDate to your Ledger.  Set it as Lookup or Auto-Enter of your Journal entry so when you create a new ledger record and insert the TransID, it includes the journal date. 

                • 5. Re: Filter portal records
                  eibcga

                  Hi LaRetta,

                  I am an accountant, and a fresh beginner with databases.  Only used FMP11 for about a month now reading lots and watching training videos.  Thanks for baring with me.  I didn't have the fields in the tables as you suggested since I was already getting the information I needed via the relationships between each table.  I'm expanding the database as I learn new skills and techniques with FileMaker — changing the structure as necessary.  If I looked at existing financial transaction solutions they would just be over my head.

                  To start my learning process, I created a database from scratch and made the accounting structure ERD you see above to follow the same basic structure of a simple invoice solution as a guide (i.e., tables for Customers, Invoices, Invoice Detail, and Products).  This database is for my purposes only to track my personal finances, and to learn FileMaker in the process.  Basically, I substituted the table names in the invoices solution as follows: "Customers" to NAMES (names can be customers or suppliers); "Invoices" to JOURNAL (aka Transactions); "Invoice Detail" to LEDGER (aka Transaction Detail or Transaction Line Items), and "Products" to ACCOUNTS.

                  Each transaction can have many transaction line times.  However, the total of all transaction line items must equal to zero (i.e., all positive amounts are "debits", and all negative amounts are "credits", and the sum of all "debits" and "credits" equals zero, indicating that the financial transactions are "in balance" (debits equals credits).  I hope this explains why the JOURNAL table has no Amount or Account fields (and LEDGER had no date field), since they "already do" when I add them on portals via the tables relationships.  But, I'm beginning to see why adding fields as necessary is important.  Your comments are really helping me understand how FM works!

                  To enter transactions, I go into the JOURNAL layout and fill in the native fields (date, memo, etc.).  I then have a portal to LEDGER and enter my transactions.  The portal has the relevant related fields from the LEDGER table to enter the transactions.  For example, I purchased auto fuel for $50, I enter a new transaction line item to account AUTO FUEL (value from the ACCOUNTS table) and enter $50 (positive amount or "debit") in the amount field, then I create another transaction line item to account "CASH" and enter -$50.00 (negative amount or "credit) in the amount field.  The total of the line items in the portal equal zero and thus, I "balance".

                  To review my accounts, I go to the ACCOUNTS layout and I have a portal with related fields from JOURNAL and LEDGER.  The portal shows the date, transaction number, reference number, name, memo, cleared, amount, (running) balance, and date crated timestamp.  I can cycle through the ACCOUNTS records on the layout and quickly see my transaction detail for each account.

                  I have "sub-summary when sorted by AcctID" report layout called "Trial Balance" with the AcctID, AcctName, and Total in the Sum-Summary Part and no Body.  It lists all accounts with their balances (i.e., total of all transaction line times within a specified date range), and the grand total equals to zero (all debits equals all credits).

                  Thanks again for your comments which are really helping me learn and understand FMP, and databases in general.  Keep the comments and suggestions coming!

                  • 6. Re: Filter portal records
                    LaRetta_1

                    As I assumed - a hybrid. I've done similar in the past but you'd need to work wonders getting it past an auditor as you know.  But such a structure is fine for smaller businesses.  Yes, you'll still want a date in your transaction lineitems (smile, Ledger).  It sounds like you're rolling and good luck to you!!  :^)

                    • 7. Re: Filter portal records
                      eibcga

                      LaRetta said "You cannot use a calculated date in Ledger for your journal date because it would be unstored and thus not available for the child side when viewing your accounts.  Set it as Lookup or Auto-Enter of your Journal entry so when you create a new ledger record and insert the TransID, it includes the journal date."

                      I take it that the above is assuming that the journal date will never subsequently be changed, which, without doing a re-lookup, would result in the journal date in both tables not being the same.  I have experimented with this and don't understand why having the journal date field result in the Ledger table stored or unstored would make any difference, but it does, as LaRetta pointed out above.  It appears to me stored means the field is storing the result (not the formula), but unstored means the field is storing the formula (and recalulates when needed)… and leaving the result as date.

                      Anyways, the issue I'm having now is that if, for example, I went back and subsequently changed a date in the Journal table, the journal date in the Ledger table will not change accordingly! (unless I re-looked up the values again).  Can somebody briefly explain "why" the above rules apply?  I'm also finding that when I add new transactions, the ledger line items are not showing the journal date in the journal date field of LEDGER, despite me having it set up as an auto-enter looked-up value from the date field in the JOURNAL table?  For it to work, I have to Re-lookup Field Contents.  Shouldn't this be dynamic?  Any ideas?

                      From a business practice or internal control point of view, I know that the journal date should not subsequently be changed.  Rather, a new transaction should be added using the same date to "reverse" the original entry, then create a new transaction entry using the correct date.  This would also provide a proper "audit trail".  Since my database will always be for my purposes only, I am aware of the "weaknesses" I currently have in my database.

                      Thanks in advance.