1 2 3 Previous Next 61 Replies Latest reply on Nov 19, 2010 2:14 PM by SwissMac

    Database Design problem

    SwissMac

      Title

      Database Design problem

      Post

      I am designing a database for our in-house admin that works fine - or at least, I thought it did. At the moment it is intended to collect information for a range of clients so one client (Tax Entity) can have many tax returns. I have the field "Tax Year" in the Table "Tax Return" and display this together with all the other fields from this Table in a Tab of a layout based on "TaxEntity". 

      However, I can't work out how to select only information from a specific year for that client. It must be possible to swap between Tax Years for any client. I am concerned that if I add a drop down menu with alternative years (eg 2008, 2009, 2010 etc) my solution at the moment only changes the Tax Year value but leaves all the other data as is.

      Somehow I need to be able to create a new tax return for a different tax year for the client, so that I don't overwrite the info from last years work but fill in blank fields instead.

      Because we sometimes work on two successive Tax Years at the same time I also need a way to select the year to be worked on somehow.

      Can anyone please help me? My brain's hurting with thinking about it too much and I need a fresh perspective: I'm sure it's quite simple but as this is only my second database my experience level doesn't help me much! I don't know if it is a Relationship design problem, a layout design problem, or if I need some scripting...

      TIA!

      TaxDatabase.png

        • 1. Re: Database Design problem
          philmodjunk

          Are you using FileMaker 11? If so, you can place a portal to TaxReturn on your tab and specify a portal filter expression such as:

          TaxEntity::gSelectedYear = TaxReturn::Tax Year

          and your portal will only show the related TaxReturn records that have the Tax Year you specify in gSelectedYear. Make gSelected Year a global field so that multiple users on your network can use this on different records and with different years and specified and not interfere with each other.

          You can make this field a drop down or pop-up menu and attach a script trigger with this step:

          Refresh Window [Flush Cached Join Results] so that selecting a tax year in the field automatically causes the portal to refresh and show the correct record(s).

          This can be also be done with older versions of fileMaker though the exact details differ a bit.

          • 2. Re: Database Design problem
            SwissMac

            Thanks for that great response!

            I've not done global variables before but it sounds like just what I want. Where do I add the Field "gSelectedYear" and where do I display it? Do I add gSelectedYear into Table "TaxEntity" as a Field there, or what? And do I have to change anything in the Relationship Entity diagram?

            I do have FMP11 but users have FMP10.

            Also, I thought Portals could only display fields in a set pattern, in rows?

            • 3. Re: Database Design problem
              philmodjunk

              Global fields can be defined in any table as long as they aren't going to be used to define a relationship. Any global field can be accessed from any layout in your file. I often define a separate table of such globals just to make it easier to keep track of them.

              I'd place the field just above the portal on the tab and format it as a popup menu of possible tax years. I'd then use the OnObject Modify trigger to run the refresh window script that I defined.

              With FileMaker 11, no changes to your Relationship graph is needed.

              PS. Global variables are a different animal. You can use them in a portal filter expression, but since you can't put them on your layout and format them with a value list for data entry, they're not the object to use for this purpose.

              • 4. Re: Database Design problem
                SwissMac

                Thanks for pointing out the differences between Global Fields and Variables for me... I didn't even notice I'd done that.

                If I put the Global Field in a separate Table called "Globals" do I still have to refer to it as TaxEntity::gSelectedYear or should I replace TaxEntity with Globals?

                • 5. Re: Database Design problem
                  philmodjunk

                  To left of :: put the name of whatever table in which you define your field. In your case, it would be Globals. When creating calculation expressions in FileMaker, you can select the field from a list by double clicking it and you'll get the correct table and field reference  entered for you and this helps keep typos from messing you up.

                  • 6. Re: Database Design problem
                    SwissMac

                    Thanks for the great info, I'm now adding it all into the layout and will post back later with results/problems. I really appreciate the clarity and speed of your replies, you've helped me a lot. I'm off to eat something now!

                    • 7. Re: Database Design problem
                      SwissMac

                      OK, I've set up the script and the Script Trigger. I must have missed a step because nothing seems to happen when I select a different year from the PopUp menu in the Global Field on the Tab. I have some basic test info in for Tax Year 2008, but if I then want to switch to a different year (to add some data from a clean sheet for the second year) selecting a different year from the Global Field has no effect I can see. I was expecting those fields with info in to empty, but they don't. 

                      Please help!

                      • 8. Re: Database Design problem
                        SwissMac

                        I found the bug (I must be learning something then!). When I added the Portal Filter I missed seeing that I had to select the table again - and it was filtering the wrong table! 

                        What you showed me is really cool though. Thanks a lot!

                        • 9. Re: Database Design problem
                          SwissMac

                          Just one small problem. My Tabbed layout has four tabs the content of which is dependent upon the filtered Tax Year action we just made for Tab 2. I have successfully managed to Filter the portals on each of these pages using the same Global Field you suggested I use. That works fine. However, when on one of the other Tabs there is no indication of which year we are talking about, even though the data is successfully showing for a specific year, it doesn't show which year.

                          I tried moving the global variable up above the Tabs altogether to where the client name details are, and while the functionality was seemingly unaffected, the field disappeared from view in all Tabs other than the one in which we first created it, the Tax Info tab. 

                          How can I see this same filter control for each of the other Tabs, ie in Missing Docs, Queries, and in Notes so that all these Tabs always show the same year data?

                          TIA

                          • 10. Re: Database Design problem
                            philmodjunk

                            I'd just copy and paste this field into all tabs where it is needed. The same script trigger will work for all, I think---unless refresh window keeps re-selecting the uppermost tab--I don't think so but there's a work around if it does--let me know. You'll just need to set up the filters on each such portal--which I think you've already figured out how to do.

                            • 11. Re: Database Design problem
                              SwissMac

                              I appear to be doing something wrong, but I can't find what or where. I copied and pasted the Global Sort field onto each Tab (actually just above the Tab, but it seems to have the same effect and seems to be treated as if it were on the Tab itself). Now, if I use one of the copied and pasted instances of the Global Field on one of the other Tabs and select a year, entering information for that year disappears when I click outside of the data entry area.

                              The only thing I can think of is that I need the field I am sorting on to be in each of the other tabs too, even though they are based on tables that do not have a year field in them (they are linked by relationship of course).

                              EG. I add some new info into the Tax Info Tab, including the Tax Year to which it applies. I then click on the Global Field to filter the Portal to show only information from that year. I then switch to the next Tab, Missing Docs, to add information about what information we still need from the client. I enter something, but as soon as I click outside of the area the new information I added into the Missing Docs Tab disappears. I'd like it to stay.

                              The Missing Docs Tab has only 4 fields in the portal: Missing Item; Requested; Reminder; Received. I do not have TaxYear or any ID fields - should I?

                              • 12. Re: Database Design problem
                                philmodjunk

                                I then switch to the next Tab, Missing Docs, to add information about what information we still need from the client. I enter something, but as soon as I click outside of the area the new information I added into the Missing Docs Tab disappears.

                                It's not a case of needing to add fields to the layout. Instead, your Missing Docs related record isn't getting the correct tax year entered so when you click out of the field or portal, the filter hides it from you. You'll either need to add the field from the related Missing Docs table to your portal so that you can specify the tax year, define this field to auto-enter the contents of your global tax year field or you can remove the filter expression on this portal and instead include the global tax year field as part of the relationship.

                                • 13. Re: Database Design problem
                                  SwissMac

                                  You'll either need to add the field from the related Missing Docs table to your portal so that you can specify the tax year

                                  Does this interfere with the instance of the same field on the other Tabs?

                                  define this field to auto-enter the contents of your global tax year field

                                  Again, I worry this will overwrite the value for TaxYear already entered in the previous Tab...

                                  or you can remove the filter expression on this portal and instead include the global tax year field as part of the relationship.

                                  If I remove the filter that shows only one year's worth of info at a time how will I know which Missing Docs are for which TaxYear? Also, I don't understand how I would "include the global tax year as part of the relationship"...can you please explain what that means?

                                  • 14. Re: Database Design problem
                                    philmodjunk

                                    I should have scrolled to the top and rechecked your graph before making those last two posts.

                                    Missing docs is related by Tax Return ID, not by tax year--a field that does not exist in your table at this time. This is also true for any portals to Queries and Tax Office you might have. Since there is no tax year field in these tables, the filtered portal approach won't work without making additional changes to your system.

                                    This creates a possible problem that needs to be looked at, before we address the "tax year" issue.

                                    Is it possible for a given tax entity to have more than one TaxReturn record for a given tax year? There are ways to link the portals on the subsequent tags to the currently related TaxReturn record, but if there can be more than one such record, we have an added complication here.

                                    1 2 3 Previous Next