12 Replies Latest reply on Oct 1, 2012 1:22 PM by philmodjunk

    Serialize based on multiple field data?

    S.H.

      Title

      Serialize based on multiple field data?

      Post

           Hi:
           I'm using Filemaker Pro 11.0v4.

           I have a database with 10,000 + records.  Many records per same clientID, and I'm looking to assign a unique delimiter assuming some of the fields within each record match. 

           For example: I have ten records with the same clientID and the same locationID and the same productID - I want all those 10 to be grouped - so that I can obtain a count of those cases where the fields are different.  So this group of 10 would be considered "1 unique delimiter." 

           On an aside, I also need to total the invoices totals per case.  So there's 10 invoices per unique delimiter that need totalled.

           Can someone please send me in the right direction of the best way to do this so that going forward it works for all the data?

           Thank you!

        • 1. Re: Serialize based on multiple field data?
          philmodjunk

               Define a summary field as the "count of" a field that is never empty such as your ProductID field.

               On a layout based on this same table, add a sub summary part "when sorted by ProductID".

               Put this summary field inside this sub summary part.

               Sort your records by ClientID, LocationID and PRoductID. Select List View.

               The summary field in the sub summary part will now show the number of records with that specific combination of these three values for every record in your current found set.

               If you remove the body layout part, you will elminate the individual row of data for each record and just have the counts for each unique combination of these three values.

          • 2. Re: Serialize based on multiple field data?
            S.H.

                 Thank you, I understand what you are saying, but I need to use this data as a subsummary part.

                 Such that, I have just the count of "groupings" based on ClientID, LocationID, and ProductID per Industry.

                 I don't actually need the count for each unique combination, but the total within the industry. - does that make sense?  And then I need to export the subsummary data into excel to make charts.

                 So say I have Retail, Manufacturing, and Hospitals as the industry.

                 1000 records with ClientID, LocationID, ProductID and Cost per record.

                 I want to know how many "groupings" there are based on the same clientID locationID and productID as well as the cost for that particular industry.

                 Does that make sense?  And then I need to export the subsummary data into excel to make charts.

                 Thanks so much!

            • 3. Re: Serialize based on multiple field data?
              philmodjunk
                   

                        does that make sense?

                   Not really. How is "industry" recorded in your table?

                   If this is a fourth field, you can either include it in your sort order or perform a find to pull up records for only one industry at a time.

                   What I am describing are sub summary values. If you use the "group by" option, you can export these subtotals to excel in a format of one row of data for each sub total.

                   And FileMaker has some charting ability so you may not actually need to export the data to get your charts. (Though I suspect Excel has more options for charting data than does FileMaker.)

                    

              • 4. Re: Serialize based on multiple field data?
                S.H.

                     The industry is one of the fields per record as well - and there are like 20 of them.  I was hoping there would be some way to code/script things so that I didn't have to run individual finds.  Thats why I thought if there was some way to create a field that added a unique delimiter per "grouping" I could then do a subsummary based on that "serial number" and then add a trailing grand summary - I really need a way to total and count the unique groupings per industry, then total and count the whole thing.  So the final report in filemaker says:

                     Industry 1

                               "unique grouping"               count of unique groupings (based on same clientID, same locationID, sameproductID)              Cost total

                     Industry 2

                               "unique grouping"               count  of unique groupings (based on same clientID, same locationID, sameproductID)              Cost total  

                     Industry 3

                               "unique grouping"               count  of unique groupings (based on same clientID, same locationID, sameproductID)             Cost total   

                      

                     TOTAL of                                         Count                                                                                        Cost TOTAL (this would be the trailing grand summary)

                      

                     I've looked into the filemaker charting, but I need to be able to hand this off to others to tweak the charts as they want and they only have excel.

                     Seems like there should be some way to do this :(

                     Thanks so much for thinking about it...

                • 5. Re: Serialize based on multiple field data?
                  philmodjunk

                       Read my post again. I said that you could include this field in your sort order OR perform a find to isolate records for a given industry. Not knowing which option would work best for you, I mentioned both.

                       Sort in this order:

                       Industry
                       ClientID
                       LocationID
                       ProductID

                       and you'll have the sub totals that you need. As long as you put Industry first and ProductID last, what I have suggested should produce the totals that you want. For your report, you can add a sub summary layout part "when sorted by Industry" so provide the headings you show in your example.

                  • 6. Re: Serialize based on multiple field data?
                    S.H.

                         I'm sorry, I'm not sure what I'm missing.

                         I have the layout set up with:

                         SubSummary of IndustryID

                         then SubSummary of LocationID

                         then SubSummary of ProductID COUNT when sorted by productID

                         I have the script sorting by IndustryID then LocationID then ClientID then ProductID

                          

                         It's grouping them, but I don't see how to get a total of the "count" per industry since that is a summary field.

                         This could be something simple, I believe I was at this point earlier today...

                         So I have "groupings" with counts of 7, 10, 25, 30, 2, 1 - and I'm looking for the final count (number of "cases" within this Industry and location) to be 6 in this example.

                          

                    • 7. Re: Serialize based on multiple field data?
                      philmodjunk

                           The same summary field can be placed in the SubSummary by IndustryID part and it will give you the total count of these records for the entire industry group.

                           But to get the "unique values count" you are describing, will require a great deal more sophistication to pull off in Filemaker 11 and older. If you have Filemaker 12, it's pretty easy to do with ExecuteSQL.

                           Am I correct that 7, 10, 25, 30, 2 and 1 represent 6 groups of records where the records in each group has a unique combination of Location and Product ID?

                           If you are using FileMaker 12: A new way to count unique values in FileMaker 12

                           If you are not using FileMaker 12: How to count the number of unique occurences in field.

                      • 8. Re: Serialize based on multiple field data?
                        S.H.

                             Thank you.

                             Yes, you are correct that the 6 groups of records represent the same ClientID, LocationID, and ProductID.

                             Looks like this takes me back to where I was trying to create serial numbers - unique for each of the 6 groups.

                             I'm using Filemaker pro 11v4. 

                             Looks like I have to create a conditional Value list, which involves creating additional tables?

                        • 10. Re: Serialize based on multiple field data?
                          S.H.

                               Thank you,  I followed the links, but I don't have a serial number and wasn't sure how the sum of the reciprocal method would work since I was sorting and using subsummaries.  The other link mentioned something about value lists if you don't have FMP 12.  Sorry, if I misunderstood i was pretty fried at that point.

                               Anyways I got approval to upgrade to 12!  That looks much easier.  Thanks again for your help!

                          • 11. Re: Serialize based on multiple field data?
                            S.H.

                                 Hello again PhilModJunk,

                                 Can you please help me with the exact code I need to write in the calculation for the ExecuteSQL command.  I followed your resolution to this to ExecuteSQL ("SELECT COUNT ( DISTINCT YourFieldNameGoesHere ) FROM YourTableOccurrenceNameHere" ; "" ; ""  )

                                 I'm sorry, I have limited experience, and am having a problem understanding the parameters...

                                 I believe I ultimately use the ExecuteSQL as a subsummary field of the IndustryID. 

                                 How do I write the command to identify and count only those where the three fields ("ClientID", "ProductID", and "LocationID") are the same within the one table I have "Services"?

                                 Am I correct to assume that I shouldn't have to write a script to sort the records if this ExecuteSQL is working correctly?

                                 Do you recommend any online classes to help novice users learn how to become developers?

                                 MANY thanks!

                                  

                            • 12. Re: Serialize based on multiple field data?
                              philmodjunk

                                   An inherent strength and weakness of the SQL string evaluated inside of ExecuteSQL is that it evaluates in a manner that is completely independent of your found set and any relationships to other tables in your database. You have to build all the relationships, find criteria etc into the SQL expression.

                                   The following assumes that for a given combination of these three values, you might have say 10 records with 4 different values in the IndustryID field.

                                   ExecuteSQL("

                                   SELECT

                                       Count ( Distinct IndustryID ) From YourTableNameHere

                                       WHERE

                                        YourTableNameHere.ClientID = ? AND
                                        YourTableNameHere.ProductID = ? AND
                                        YourTableNameHere.LocationID = ?

                                   " ; "|" ; "|" ; YourTableOccurrenceName::ClientID ; YourTableOccurrenceName::ProductID ; YourTableOccurrenceName::LocationID )

                                   This assumes, however, that you want this count over all records in your table. If you first performed a find to bring up a specific group of records for your report, you'd need to include additional criteria to limit the count to the same records used for your report's found set.

                                   ExecuteSQL is a new function that we are all still exploring. I don't know how practicle this approach will be to produce the needed sub totals. It may take much too long for your screen to update--depending on the number of records.

                                   I recommend two resources for working with ExecuteSQL: SeedCodes SQLExplorer is free and can help you get the basic syntax correct when setting up your SQL expression. And the relevant documentation on the Select function--the only one you can use with ExecuteSQL can be found in the ODBC and JDBC Guide found in Product Documentation under FileMaker's Help menu.

                                   I am not sufficiently familiar with the corrent training resources to make any recommendations for or against.