4 Replies Latest reply on Mar 24, 2014 2:50 PM by GaryJohansen

    Automated enviroment setup?

    GaryJohansen

      Title

      Automated enviroment setup?

      Post

           I need a bit of help here. I’m writing a solution to track large sales forces, in different territories for different companies.

           The sales transaction data is similar in the different locations. So once I parse the names (which come in a single field) I need to aggregate sales in 2 categories, for currents sales (YTD), Last years production and comparison showing direction (increasing or decreasing).

           So currently I have a salesperson file, that relates to the current sales data 2 ways (2 instances of the sales file a relation to each), I create 4 instances of the last years data and create a separate relationship to each, a layout in the salespersons table that includes necessary fields from all the related tables, so I can loop through the salesperson file and gather the data.

           Works fine, but it would be great to save the setup so I could set it up easily. In other database programs I can create view of all open tables, relationships, etc, save it and call it back and when I need to. So I’d only need to setup the environment once for each territory , using the salesperson table in each one linked to different tables.

           Is there an easy way to do the same thing in FMP?

           Thanx for your time and info.

        • 1. Re: Automated enviroment setup?
          philmodjunk

               Sorry but there's not enough detail here to understand what you have set up and what you need to do.

               

                    So once I parse the names (which come in a single field)

               A single field in what? Why are all the names in one field?

               

                    I create 4 instances of the last years data and create a separate relationship to each, a layout in the salespersons table that includes necessary fields from all the related tables, so I can loop through the salesperson file and gather the data.

               That's pretty vague and there's no clear reason given why you have to "loop through the data". What you describe sounds like summary reports, but such usually do not require looping through data--a slow way to get things done--in order to produce the desired results.

               Why do you need to set up a "different environment for each territory"? Why not use one set of tables and relationships that match to all your data and then manipulate the match field values to match to data from different territories? That way one set of tables, relationships and layouts can report data for any selected territory--which also simplifies and unifies your design.

          • 2. Re: Automated enviroment setup?
            GaryJohansen

                 Let me start out clarifying that I'm not distributing printed reports but standalone databases that each user will install on their computer.

                 

                      A single field in what? Why are all the names in one field?

                 Where I'm getting the data the name is contained in a single field (Robert John De La Renta Jr.). My users want to search by First Middle and Last names

                 Each territory/company will have slightly different transaction records.

                 Think of it as a CarMax store where management wants to see how much each salesperson is responsible for buying and/or selling. So I have a salesperson file that is linked to the transaction files, There is a transaction file for every item in inventory with the sales and purchaser ID. They want to track counts of purchases and sales and dollar amounts of each for each salesperson for Year To Date, the previous year and if the salesperson is trending up or down from last year.

                 So I use the salesperson file to link to the transaction records, have a found count field and a summary sales field in the transaction file. I create a relationship to the salesperson ID located in the sale field and a different relationship to the salesperson ID in the purchase field. Doing the same thing for the previous year’s transaction records. This way as I loop through the salesperson file I can take the number of purchase and sales transactions and the total dollar amounts for each salesperson and store it in the salespersons record. Salesperson file has fields for the sales and purchase values salesperson info (salesperson ID, store ID).

                 Now it’s easy to find out sales trends and rank by salesperson, store, company, and territory.

                 It is not acceptable to distribute the transaction records, or one company stats to another company merely the assembled data.

                 Clear as mud aye?

            • 3. Re: Automated enviroment setup?
              philmodjunk
                   

                        This way as I loop through the salesperson file I can take the number of purchase and sales transactions and the total dollar amounts for each salesperson and store it in the salespersons record.

                   This loop is not needed to produce this result. The relationship makes it possible to access those totals from the SalesPerson Table and thus any layout based on that table. There's no real need to store that data in the SalesPerson table at all though it could be done.

                   

                        It is not acceptable to distribute the transaction records, or one company stats to another company merely the assembled data.

                   This is the key detail missing from your original that you are sending this data back to individual companies and that you need to omit any data that does not belong to them. (Note: If you published this data to the web, Record Level Access controls could limit each company to only seeing their data and then you avoid this issue.)

                   This sounds like something simply handled by generating copies of your file or group of files. (You seem to use "file" and "table" interchangebly, but a single FileMaker file can consist of many different tables.)

                   a) Perform a find on each such table/file in your system to produce found sets of records for a specific company

                   b) an Import Records in a script in a saved clone (no records are saved with the clone option) could then import all the relevant data from your file(s) of combined data into the tables/files of the copy(s).

              • 4. Re: Automated enviroment setup?
                GaryJohansen

                     Thank you again.

                     In the future I will be more specific in my use of file and table, thanx for pointing it out