6 Replies Latest reply on Feb 26, 2010 2:26 PM by macwasp

    Need help with search and related data issue

    macwasp

      Title

      Need help with search and related data issue

      Post

      I posted the topic below a while back when I was using FMP7.  Now I have FMP11 and would appreciate some help in following through on a suggestion made by one of the contributors. He/She said "Another option, a somewhat simpler one, is to use a table of years with a global gCustomerID field to summarize the sales values by year for the selected customer - but you need to keep the global synchronized to the currently viewed customer. This is easy to do in version 10, but in previous versions it means restricting the user to scripted navigation."

       

      So how does this work in 11?  I want to produce a browse screen where I can scroll from one customer to the next.  Each time a portal or lookedup field with total sales data for a given year is updated, so company x 2005 $100, 2006 $200, 2007 $300 etc. then the next customer etc.

       

      Can I create a field within my sales revenue database (customers are in a separate file for a number of reasons) that summaries annual sales for a given year against a unique customer id?

       

       

      Hi, I'm using FmPro 7 on XP to build a sales analysis database and I need some help with data filtering and presentation. Here's the setup; There are two files; the first is a list of customer name and address details (customer) , the second has the customer name and product purchases over the past four years (sales). The files are related by a unique customer_id field. The relationship works fine and in a portal I can list and review sales data from the other file. What I want to do is display a total sales figure for each year of business in the customer file - whilst in browse mode. So for company ABC Ltd I could see 2007 Sales $XXX, 2008 Sales $XXX, etc all on the one screen. I've setup an annual_sales summary field in sales but this either gives me the total for the file or the total based on a Find query. How can I create a field for each business year that shows me the total sales individually for each customer, that can then be viewed in customer?

       

       

        • 1. Re: Need help with search and related data issue
          comment_1
            

          The simplest way to do this is to produce a report from the Sales table, summarized by customer and by year.

           

           

          BTW, I'd suggest putting both tables in the same file, unless you have some reason not to.

          • 2. Re: Need help with search and related data issue
            macwasp
              

            Thanks for the response, however this is not quite what I want.  I can already pull this information in a sales report as suggested but I want something more dynamic.

             

            I have to keep separate customer and sales files as the data comes from two sources and is updated regularly.  To combine them is impractial.  The key point, which I may have not made clear, is that I want to flip through customer records in browse mode and see the annual sales fields update for each company I look at.

             

            I could present the customer data in the Sales browse which may help. Maybe a self-relationship in Sales to total the customer sales records by year would work, listing 2007, 2008 and 2009 sales per company and placing contact info in a portal?

            • 3. Re: Need help with search and related data issue
              comment_1
                

              macwasp wrote:
              I want to flip through customer records in browse mode and see the annual sales fields update for each company I look at.

              That too is possible, just more complex. The problem here is that you want to see only one record per year in the portal to Sales.

               

              I am not sure I can explain the solution within the confines of a forum message, so I'll refer you to these:

              http://www.fmforums.com/forum/showtopic.php?tid/153197

              http://fmforums.com/forum/showtopic.php?tid/157638/post/157908/#157908

               

               

              Another option, a somewhat simpler one, is to use a table of years with a global gCustomerID field to summarize the sales values by year for the selected customer - but you need to keep the global synchronized to the currently viewed customer. This is easy to do in version 10, but in previous versions it means restricting the user to scripted navigation.

               

              • 4. Re: Need help with search and related data issue
                macwasp
                   How would this table of years/gCustomerID field work?  Scripted navigation is not a problem as I am the main user of the database
                • 5. Re: Need help with search and related data issue
                  comment_1
                    

                  Here's a simplified example:

                   

                   

                  1. Create the Years table with the following fields:

                   

                  • Year - Number, Auto-enter serial number starting at, say 2005 (the earliest year for which you'll have data)

                  • gCustomerID - global (same type as your CustomerID in the Customers table)

                   

                   

                  2. Define these two relationships (using two occurrences of the Years table):

                   

                  Years::Year = Sales::Year

                  AND 

                  Years::gCustomerID = Sales::CustomerID 

                   

                   

                  Customers::CustomerID x Years 2::Year

                   

                   

                  3. Add a calculation field cSum to the Years table (result is Number) =

                   

                  Sum ( Sales::Amount )

                   

                   

                  4. Place a portal to Years 2  on a layout of Customers, showing the Year and cSum fields.

                  • 6. Re: Need help with search and related data issue
                    macwasp

                    Thanks - this is a great solution!