1 2 Previous Next 28 Replies Latest reply on Apr 7, 2012 11:51 PM by GuyStevens

    Problem with merge text field

    mora_1

      Title

      Problem with merge text field

      Post

      I have a merged text field that contains calculations. It displays the top seling product and is in the footer in a layout report:

      TOP PRODUCT <<PRODUCT>> <<max COMMISSION>> (<<max QUANTITY>>)

       

      The problem is when the layout is active, and if I click on any summary in the body of the layout, the merged text field displays the data that is associated with what was clicked, instead of always displaying the top selling product. So I have to scroll to the top product and click on it to get the correct data to display. If I don't click the layout, the data is accurate.

       

      I have tried to fix this, but have not been successful. Can anyone please help me? Thanks in advance.

        • 1. Re: Problem with merge text field
          GuyStevens

          Maybe you could try this:

          - create a calculation field in your table. You could  name it: TopSellingProduct

          - Set your calculation in the options dialog and click "Storage options"

          - enable "Use global storage"

          Now you have a calculation field that performs your calculation. Because of the "Global storage" the value of that field is the same for every record.

          - Put that field as a merged field in your footer.

          Let me know if that solves your problem.

          • 2. Re: Problem with merge text field
            philmodjunk

            You might define a summary field as the "maximum" of this field's value and put it in the footer.

            • 3. Re: Problem with merge text field
              mora_1

              Thanks for your quick reponse Phil and DaSaint.

              Tried both your suggestions but it didn't work. When I define a summary field as the "maximum", the data displayed still changes if I click on the layout. And Global Storage somehow does not display the correct data.

              • 4. Re: Problem with merge text field
                GuyStevens

                Could you give us a little more information on the tables you are using and possible erlationships.

                Or where your data is coming from?

                It's a little hard to try and find the problem when we don't really know what you are trying to do.

                If you could give us a striped down version of your file, that would be the best.

                But some more info or some screenshots might also help.

                • 5. Re: Problem with merge text field
                  GuyStevens

                  I made you a very simple, tiny example file.

                  It shows what I think you are trying to achieve in a very simple way.
                  Now I'm sure your file is more complex, but without more info I can't know where the problem might be.

                  http://dl.dropbox.com/u/18099008/Summary_Test.fp7

                  What I made is a very simple product list with products that all have a price. And I made a note on the header that shows the price of the most expensive product.

                  I did it using a summary field and once using a calc field to show the text.

                  Please let us know if this is helpful.

                  • 6. Re: Problem with merge text field
                    mora_1

                    Thanks for your example DaSaint.

                    I have sent you a sample file (http://dl.dropbox.com/u/974264/summary_test_copy1.fp7)

                    These are the issues:

                    #1. TOP PRODUCT: <<c_maxProductType>>  US$<<c_maxProductSales>>  <<c_maxProductQty>> units

                    This merge field changes when you click on the category summaries (in the purple section). This was my original problem at the start of this message.

                    #2. cTOTAL_MTH_SALES

                    This total has started to cgange also! To get it to display the accurate total, I have to go to manage database and double click the calculation. This did not happen before. Sigh!

                    #3. Also, the summaries are according to Product Types. But I have been trying to get the Product Type summary to appear in the layout, according to Product Sales. I did create a script button to run a sort script but that messed up the cTOTAL_MTH_SALES. I go rid of the script button but I think that is why the cTOTAL_MTH_SALES got messed up (problem #2).

                     

                    Would really ppreciate some help here....and I apologize if this seems really a basic issue. Thanks in advance from a newbie.

                    • 7. Re: Problem with merge text field
                      philmodjunk

                      I looked at your demo file and didn't find any problems with it. It as three records with prices of 10, 20 and 30. The summary field correctly returns a maximum price of 30 and clicking on different records or into different fields does not alter the value returned--as expected.

                      What exactly are you doing when you see this total change?

                      (Summary fields summarize the records found in the current found set, the value should not change unless you either edit a field in one of the records in the found set or you add/remove records from the found set.)

                      • 8. Re: Problem with merge text field
                        mora_1

                        Hi Phil - I think you were looking at the sample DaSaint sent to me. My sample file name is: summary_test_copy1.fp.

                        • 9. Re: Problem with merge text field
                          philmodjunk

                          You are correct. I don't see any link to a file of that name in this thread. How does your file differ from the demo?

                          Clicking on different records should not modify the results displayed by summary fields, though editing them, or modifying the records in your found set will affect the totals they display.

                          • 10. Re: Problem with merge text field
                            GuyStevens

                            Hey Mora,

                            I just started looking at your file and the first thing I noticed is that you don't have a single Id field set up.

                            And your relations are based on text fields. That might be a first problem.
                            You are better of using a number field as a Unique ID field with automatic serial numbering turned on.

                            Also, what is your intention with the "Product List" and "Product Range Data" tables?
                            They seem almost the same.

                            Is it the intention to create a list of Products and a list of categories? Because that can be made slightly differently.

                            Also for your Max Product Type you use the calculation: "Max ( PRODUCT TYPE ; PRODUCT TYPE )"

                            You don't need to name your field twice. But the calculation is set up to result in a "number" but your Product type field is a Text field.

                            I'm not done looking at your file and I would like to fix it a little. I'm going to continue on it tonight.

                            • 11. Re: Problem with merge text field
                              mora_1

                              Hi DaSaint - I feel you will find more things that need to be fixed. I only started with databases and fmp in Feb, and there are heaps I'm still figuring out. I really appreciate your help. Look forward to your next post!

                              • 12. Re: Problem with merge text field
                                GuyStevens

                                Hi mora, I'd love to get you started in the correct direction.

                                But I'm having a few problems understanding the data.

                                You have:
                                - A product List
                                - Product Categories
                                - Design Data
                                - A Store list

                                I would like to fix these first before I dive into the sales data.

                                Explain to me a litte what you would like to do. I assume you need a list of stores, and every store sells a certain product.
                                Every product can be in a certain categorie, but not every store sells every category, sometimes they only sell certain product items from a certain categorie.

                                I don't entirely understand the "Design" part. Is that per product? Or how does that work? Designs appear available in certain stores, but how are they linked to products?

                                • 13. Re: Problem with merge text field
                                  mora_1

                                  Hi DaSaint

                                  Design Data = a record of the designs created. New designs are added to the list. These are the designs that have been made into fabrics, that will then be made into products.

                                  Yes, not all stores sell everything. Some stores are just kitchen shops sp will stock only kitchen ware.

                                  Product List and Product Range: I started with just Product Range. But i had to create the Prodict List for the conditional drop down list (used for the sales data). We have a huge list of products in each category.

                                  What I need to do is produce monthly reports to show:

                                  1. Best selling store

                                  2. Best selling design

                                  3. Best selling product

                                   

                                  Phil, the sample I made is in http://dl.dropbox.com/u/974264/summary_test_copy1.fp7

                                  • 14. Re: Problem with merge text field
                                    GuyStevens

                                    Hi Mora, I'm about halfway trough your database :)

                                    I started fixing the ID's etc. and now I just started messing around with the reports.

                                    It's getting a little late here, so here is my current version.

                                    I'm going to look a little further at the reports. But I don't know if your version of a report is going to be possible.
                                    I think you'll need to make seperate reports to determine what the top selling store is. What the top selling Product is and what the top selling design is.

                                    (But I'm stil brainstorming on that)  :)
                                    I'm having problems with the sorting.

                                    In the mean time, have a look here:
                                    http://dl.dropbox.com/u/18099008/summary_test_DaSaint.fp7

                                    I noticed you made a lot of calculation and summary fields, I guess you must have been experimentng. I hope this makes a little clearer.
                                    I don't know how far along you are in using layouts but I made a few.
                                    I always keep my tables separate from my layouts because layout objects like checkbox sets and dropdown lists can sometimes hide the real data that's in a field.

                                    I'm not yet happy about the sorting, but I'll try and do some more tomorrow.

                                    1 2 Previous Next