13 Replies Latest reply on Jan 25, 2016 2:09 PM by BruceRobertson

    Newby needs help, please

    sseelbinder

      Yes, I admit it, I am a newby to FileMaker, converting from MS Access.  I have a question, which I will very much over simplify to make it simple (I think?)  Running FileMaker on a Mac.

       

      Lets say I have a database with the following three fields (columns):

        Month (Jan, Feb, Mar, etc)

        Serial Number (alpha numeric which can be matched month to month)

        Dollars (Different amounts per month per serial number)

       

      What I am looking to do is compare two (or more) months, matching serial numbers, and finding the dollars difference (in percentage) or flag dollar differences greater than say 10% per serial number.

        • 1. Re: Newby needs help, please
          DanielShanahan

          Hi sseelbinder - welcome to FileMaker!

           

          While I and others here are advocates of FileMaker, given your three column data, I wonder if you would be better off using a spreadsheet.

          • 2. Re: Newby needs help, please
            sseelbinder

            Thanks for the reply.  Actually I am using a spreadsheet now, but as I stated the example is simplied from a spreadsheet some 26 columns wide and  96000 rows.  Hoping to find a tool which can perform these tasks simpler and quicker than a spreadsheet.

             

            -Steve

            • 3. Re: Newby needs help, please
              DanielShanahan

              Hi Steve,

               

              I suspect the first order of business is to normalize the data.  You may be familiar with this as you've used MS Access but in case you're not, you can read up on it here: Database normalization - Wikipedia, the free encyclopedia

              • 4. Re: Newby needs help, please
                sseelbinder

                Thanks. Looking for clues to set up a report or script to accomplish the task at hand.

                 

                Sent from my iPhone

                • 5. Re: Newby needs help, please
                  DanielShanahan

                  sseelbinder wrote:

                   

                  Thanks. Looking for clues to set up a report or script to accomplish the task at hand.

                  I'm not quite clear on what that means.

                   

                  Perhaps if you post the spreadsheet it may be easier to help.

                  • 6. Re: Newby needs help, please
                    BruceRobertson

                    Hi Steve,

                     

                    I suspect the first order of business is to normalize the data.

                    I sort of agree; but it sounds like it may in fact be already normalized, at least in concept.

                    That is, he describes three fields: Month; SerialNumber; Amount.

                    It's just that, in original spreadsheet form, the data isn't displayed that simply.

                    Also - I suspect that there must be a year column as well. Or that such a field would need to be created.

                    The first tough job will be getting the spreadsheet data into the normalized format.

                    There will also be some questions about reporting. For instance, the 10% difference figure.

                    Compared to what? Compared strictly to the prior month? Finding outlier month(s) where most months are in a less-than-ten band?

                    • 7. Re: Newby needs help, please
                      sseelbinder

                      The compare would be based on selected months, at least two - maybe more if possible.

                       

                      Matching serial number from the months selected, then looking at their corresponding $values.

                       

                      As I am currently working with 2015, I suppose if I needed to compare Dec2015 with Jan2016 then I may need to include the year unless I happen to know which Jan o compare with (2015 or 2016).  The months are currently text format, not a month format, with the 3 letter abbreviations.  Serial number is alphanumeric, and amount is a number at 2 decimals.

                      • 8. Re: Newby needs help, please
                        brucewilson

                        Hi sseelbinder,

                        You will want to be looking at Summary fields, the Min, Max, and Average functions, and probably the GetSummary function.

                        Start with manually producing the results you want using Find, Sort and Layout Setup.

                        Once that works you can add scripts to streamline the process.

                         

                        When you have more specific questions we'll be able to give you more detailed answers.

                        (Does 2 or more months = January + April + November? or March 2013 + March 2014 + March 2015? or July through December?)

                         

                        This 2¢ was contributed by

                        Bruce Wilson

                         

                        <aside - I reckon importing the data is the easy part >

                        • 9. Re: Newby needs help, please
                          sseelbinder

                          New question format...

                           

                          Trying to keep this over-simplified...

                          Let say I have a FMP DB with three tables, OCT15, NOV15, DEC15

                          Each with Fields "Serial" and "Amount"

                          Which have records of matching "Serial" (text) and different "Amount" (numbers)

                          I want to compare the three tables data records in a new layout - matching the "Serial" fields records and finding the difference (in percent) of the "Amount" fields.

                           

                          (Is there a way to attach this very small 201kb DB to this discussion?)

                          • 10. Re: Newby needs help, please
                            BruceRobertson

                            "Let say I have a FMP DB with three tables, OCT15, NOV15, DEC15"

                            First of all; you don't want to do that.

                            Month/Year should be a FIELD in the underlying table.

                            Secondly - look at the upper left part of the reply here and notice "Use Advanced Editor"

                            Once you are looking at the advanced reply dialog, you will see the "Attach" option in the bottom right.

                            • 11. Re: Newby needs help, please
                              jjfcpa

                              This seems like a pretty typical "use case" for any business trying to look at sales data.  We've dealt with these kinds of reporting requirements many times, but we haven't used FM for this type of requirement, so I'm going to tell you how I would approach this if I were using an SQL database.

                               

                              I would change the structure of your table to the following, just for added flexibility:

                               

                              YEAR - text (YYYY)

                              MONTH - text (MM - pad left with "0")

                              SERIAL - doesn't matter

                              AMOUNT

                               

                              Once this table is populated, I would do one SQL to select the YEAR and MONTHS that I want to compare.  I guess you could accomplish the same thing with a FIND in FM. 

                               

                              Now that I have those records isolated, I would add them to another table (call it SUMMARY_DATA) whose structure would be as follows.  If you used an SQL statement, you'd have already summed the months and could just create records and add to the AMOUNT1 field.

                               

                              AMOUNT1

                              AMOUNT2

                              DIFF_AMT - calculated field AMOUNT1 - AMOUNT2

                              DIFF_PCT - calculated field DIFF_AMT / AMOUNT1

                               

                              Next I would then do another SQL to grab the comparison YEAR and MONTHS and add them to the SUMMARY_DATA.  In SQL parlance, you could do an SQL and UPDATE statement to accomplish this.  In FM, I'm guessing (no expert here), you'd have to

                              write a script to do a FIND for each serial number and then add the data to the AMOUNT2 field.

                               

                              After adding all the data to the SUMMARY_DATA table, you could then do a FIND for the DIFF_PCT > WHATEVER_YOU_WANT.

                               

                              Perhaps you could do this with some relationships in FM but that's beyond my capabilities and perhaps someone else will give you an idea on how to do that. 

                              • 12. Re: Newby needs help, please
                                sseelbinder

                                There is no "Use advanced editor" nor "Attach" using either Safari or Firefox.  (See screen print below)

                                Screen Shot 2016-01-25 at 1.33.59 PM.png

                                • 13. Re: Newby needs help, please
                                  BruceRobertson

                                  Maybe a forum rep can explain. Your experience certainly doesn't match mine.

                                   

                                  reply_options.png