9 Replies Latest reply on Sep 3, 2015 10:59 AM by

    Kinda embarrassed to ask . . .

    gregew

      I've been on board with Filemaker since 9.0 but it's been so long since I've created a true relational solution from scratch that I have to admit that I need some basic first-steps direction.  I have FMPro12Advanced, and I have pretty good skills in creating tables, forms, calculated fields, reports and all the things that follow AFTER the necessary tables have been determined and the relationships have been graphed.  But making all the correct structural decisions for Tables and Relationships right out of the gate has me anxious and feeling stupid.  (I'm not a developer, as you can guess, but I love learning and using FM for my under-funded nonprofit clients.)

       

      So if you have the time and willingness after reading what I want to do to either steer me to an appropriate tutorial or even plot out the steps for me, here are the important details of my mission: to create an on-going repository of annual financial and programmatic activities for 25 nonprofit organizations:

       

      1.  There are 25 Organizations, each with its own usual basic permanent data such as name, address, fiscal year, etc.

       

      2.  Each Org files an Annual Report (using an identical PDF form) with specific data such as their year's revenue, expenses, numbers of people served, etc.  I have over 50 numerical data points to track that change year-to-year.

       

      3.  I want to be able to store these data points for each Org by going to its entry on the Layout, selecting the Year, and entering its data.  So anytime in the future I'll be able to go to an Org's page and by selecting a particular Year I'd be able to see the data points for that Year. (Are we talking about some impossibly huge Portal here?)

       

      4.  I'm not concerned about the physical size or "clutter" of so many entry fields--we're doing this for internal use only by a couple of data wonks like me.  Anyway, I do have design experience with which I usually spend endless hours to nudge, tweak, size, color, etc the elements on my Layouts. There are no servers involved--only my desktop 'master' from which I'll send out the annual updated Runtime to a couple of people.

       

      5.  I have created three Tables:  One called "ORG" (with its handful of permanent descriptive fields), another called "Fiscal_Year" (assuming I'd only be storing Fiscal Years as a text field here) and the third "Fiscal_Year_Data (in which I created my 50+ fields of numeric data).  I've already entered the 2014 data for the 25 Orgs into this last Table.  No relationships have been built, but each Table has its obligatory ID serial number field.

       

      So, again, this assignment seems to be so simple I'm hesitant to even ask, but I'm not sure where else to turn.  I suffer information overload when I dive into my FMPro tutorial book and "Missing Manual."

       

      Thanks in advance for any advice, direction or downright assistance you can provide.

       

      Best,

      Greg

        • 1. Re: Kinda embarrassed to ask . . .
          erolst

          Another approach would be to define your data points in their own table, then, rather than having 50 fields, create one related record per year / data point; so

           

          Organization --< OrgYearDataPoint >-- DataPoint

           

          would hold 50 records per organization and year (created by a script).

           

          This is more flexible, for display purposes as well as in data analysis. (Consider that you can summarize any number of records with a summary field and a script; try that with any number of fields from any number of records.)

           

          gregew wrote:

          But making all the correct structural decisions for Tables and Relationships right out of the gate has me anxious and feeling stupid.

           

          Yes, “measure twice, cut once” – but that part can really be fun, and a challenge. False starts, revised assumptions and startling revelations are par for the course; “there is no reason to panic” …

          • 2. Re: Kinda embarrassed to ask . . .

            No need for 3 tables.  Put the FY as a field in the 2nd table with the 50 other datapoints.  You can do a find, put in the year and do a summary on your found set.

            • 3. Re: Kinda embarrassed to ask . . .

              Thanks, DCovington.  I like the simplicity of your idea (because I don't have scripting, as erolst suggests I use, in my toolbox), but I need some clarification...if you don't mind...

               

              I'll try to keep my question as simple as possible:

               

              If I have one table, ORG, with Name and City as fields;  and my other table, ANNUALDATA, with FiscalYear and the other 50 datapoints as fields as you suggest, then on a data entry Layout where the ORG's Name appears along with all the fields from ANNUALDATA, how would I be able to store the different data that are associated with each of the different FiscalYears? 

               

              In other words, if FiscalYear is simply one of the other 50 datapoint fields in the same table, then next year how do I create another FiscalYear with its associated datapoints for each Org?  I can't just enter new data next fiscal year for an Organization without changing (losing) what's already in there from this year.

               

              I know I'm missing something basic--evidence that I have this big hole in my Relationships 101!

              • 4. Re: Kinda embarrassed to ask . . .
                erolst

                Guest wrote:

                In other words, if FiscalYear is simply one of the other 50 datapoint fields in the same table, then next year how do I create another FiscalYear with its associated datapoints for each Org?  I can't just enter new data next fiscal year for an Organization without changing (losing) what's already in there from this year.

                In this data model, each record has fifty fields for data points, plus a foreign orgID and a year.

                 

                This means: you create a new data record per annum, specify the orgID and the year, and fill in the data point fields.

                 

                Guest wrote:

                Thanks, DCovington.  I like the simplicity of your idea (because I don't have scripting, as erolst suggests I use, in my toolbox)

                It better be – at least a bit.

                 

                You need nothing more than a script that creates a set of related data points for an org. Here also each record has – as in the other data model – an orgID and a year, but only describes a single data point.

                 

                This means: you create a new set of data point records per annum.

                 

                So in this model, the set of all records with the same orgID and year corresponds to a single record in the other model.

                This has a number of advantages over the “fifty-field-approach” – which you will note as soon as you try to summarize numerical data points.

                 

                See the attached sample file.

                • 5. Re: Kinda embarrassed to ask . . .
                  erolst

                  Here's the promised sample file (I was not able to edit my other post).

                  • 6. Re: Kinda embarrassed to ask . . .
                    erolst

                    Sorry about that missing file (it was attached, but doesn't show); it seems the forum has some problems ATM.

                     

                    Will post it ASAP.

                     

                    EDIT: And here it is.

                    • 7. Re: Kinda embarrassed to ask . . .
                      gregew

                      Sorry I've been diverted for a couple weeks, erolst, so thanks again and I'll begin to get back to this next week.  But in the meantime, it appears that the file you've been trying to attach still doesn't show up in this thread.  Is that a problem with this forum, or am I not seeing it (or not finding it somewhere on the page) on my end?

                      • 8. Re: Kinda embarrassed to ask . . .
                        erolst

                        The attachment is there; but it seems it's only in the thread itself, not within posts shown in an overview or other summary views.

                        • 9. Re: Kinda embarrassed to ask . . .

                          Roger that.  I was logged in, it wasn't there, so now I'm NOT logged in and see it!  I didn't know there were two ways to view Forum posts.  You'd think if one were logged in you'd see everything!

                          I'll figure out how this forum works someday...although don't get me wrong--I'm very happy that I always get great responses.  I wish I were able to reciprocate.

                           

                          Thanks!