9 Replies Latest reply on Nov 30, 2011 1:17 PM by philmodjunk

    lookup for a summary total field FM10

    cules

      Title

      lookup for a summary total field FM10

      Post

      The lookup value is correct on the first lookup. Once the I change the value for the summary field and re-lookup, the look up value doesn't update. It still pulls the old value. Can someone help please?

        • 1. Re: lookup for a summary total field FM10
          philmodjunk

          You'll need to describe what you have set up in more detail. "look up" is something that can be done in more than one way in FileMaker and the results/behavior can depend on the method used.

          Is there a relationship between two tables or the same table (self join) involved? If so, what is the relationship you have set up?

          You mention a summary field. What values are being summarized?

          How, exactly, do you "re-lookup" the value? Is this the menu option in the records menu or some other method?

          • 2. Re: lookup for a summary total field FM10
            cules

            This is actually two files. One for the Production Report and the other is the Job Summmary Report. I have a summary field for a report from the Production Report. Each record has a quantity produced and the list report has a total of quantity produced for the records found. The Look up is for the Job Summary file. I have it set up to look up for the Total of Quantity Produced. I've tried changing the Storage from none to all and it didn't make a difference. It seems like it saves the data from the first look-up and just enters the old data. The other type of field like number, text, date, time are looking up properly. I currently have FM 6 version running and this set-up works well. I'm testing this automatic conversion to upgrade to FM 10 ADV and this is one of my problem. I hope this clarifies a little bit.

            • 3. Re: lookup for a summary total field FM10
              philmodjunk

              You haven't indicated how Production Report is related to Job Summary Report. How is this defined? What data types are the key fields used in this relationship?

              I've tried changing the Storage from none to all and it didn't make a difference.

              This suggests that you have a calculation field and you have not posted the actual calculation defined for this field. This is not actually needed here. You can add the summary field from Production Report directly to your layout in the Summary Report file as long as you have a valid relationship to the table in the Production Report defined in your Summary Report file.

              With fileMaker 7 and newer, you can define the tables all in one file instead of in separate files as was required for FM 6. You can stay with the one file on table structure if you wish for your current solution, but you'll find most posts here now refer to "table" where they might have referred to "file" in the days of FileMaker 6 and older.

              • 4. Re: lookup for a summary total field FM10
                cules

                The production report is related by Relationship. This is a number field called sales order related to the job summary Sales Order. There are multiple Production Report with the same Sales Order Number. 

                The field I'm trying to look-up is a Summary Field Type from Production Report to Job Summary number field Type. 

                I was trying to change some of the calculation field that totals a summary field for the storage option. Either a summary field that totals a number field or calculation field didn't make a difference. The first time it looks up properly, then the second time would be the previous data.

                • 5. Re: lookup for a summary total field FM10
                  philmodjunk

                  Then the relationship would appear to be:

                  Job Summary Sales Order::SalesOrder = Production Report::SalesOrder

                  The field I'm trying to look-up is a Summary Field Type from Production Report to Job Summary number field Type.

                  Does this mean that you have used the Looked up value auto-enter field option to copy the value of the summary field in Job Summary Sales Order into the number field in Production Report?

                  This method copies the current value from summary field in Job Summary Sales Order to the number field in Production Report at the time the Job Summary Sales Order::SalesOrder field was last edited. It will not update automatically in FileMaker 10 nor in FileMaker 6.

                  Try this method instead to get a value that should update automatically:

                  On a layout based on Production Report, add the Summary field from Job Summary Sales Order directly to the layout. You can do this with the field tool. When you drag the field tool onto the layout and release the mouse button, the specify field dialog appears. Select Job Summary Sales Order from the drop down, then select the summmary field.

                  Changes to records in the Job Summary Sales Order file should then result in automatic updates of this total.

                  If you need to use the total of related records in a calculation, just refer to Job Summary Sales Order::SummaryField like you would a field in the Production Report table. (this calculation will automatically become an unstored calculation due to the reference to a field in a related table.)

                  • 6. Re: lookup for a summary total field FM10
                    cules

                    Does this mean that you have used the Looked up value auto-enter field option to copy the value of the summary field in Job Summary Sales Order into the number field in Production Report?

                    Yes, but the auto enter look up is from production report summary field into the job summary number field.

                    I'm looking for a snap shot of the summary field. This could be the same Sales Order but with only certain dates, lot, etc. This allows me to produce multiple reports with different sales order, parts, operators, etc.

                    I was able to find a set of records from the Production Report and pull the summary field to the Job Summary by auto-enter looked-up value. I would normally create another record in Job Summary and find the next set of records from Production Report to pull the summary fields again. On Filemaker Pro 6, this was possible. On Filemaker Pro 10, I can do this on the first Record look-up on Job Summary, then the second new record I create and look-up after finding the set of records from Production Report, the look-up value is from the first look-up since Filemaker Pro application was open. The only way I can do the Job Summary for now is to Close the application and re-open it for every set.

                    • 7. Re: lookup for a summary total field FM10
                      philmodjunk

                      I think I need to better understand exactly what you are trying to do here.

                      What does one record in Job Summary Sales Orders represent?

                      What does one record in Production Report represent?

                      The looked up value settings function exactly the same way in Filemaker 5 or earlier though version 11 so I don't see why it would behave differently in FileMaker 10 than in 6. (A while back I took two version 5.5 systems and converted them to version 10. They had numerous fields with looked up value field options and they needed no changes to work correctly in version 10.)

                      I know you said they are numbers, but just to be sure: Both SalesOrder fields are of type number and only numbers are entered in them? (Filemaker 6 and older versions index text fields differently than newer releases so text field based relationships sometimes have a problem after conversion.)

                      This has me confused:

                      On Filemaker Pro 10, I can do this on the first Record look-up on Job Summary, then the second new record I create and look-up after finding the set of records from Production Report, the look-up value is from the first look-up since Filemaker Pro application was open.

                      With a looked up value field option, you do not have to "find" any records in any table. It relies on a relationship to match the current record to records in the related table. A summary field referenced in this fashion will return the total of all matching records in the related table. (In this case all records in the production summary with the same SalesOrder number as the current record in the Job Summary Sales Order table.)

                      The only way I can do the Job Summary for now is to Close the application and re-open it for every set.

                      Closing and reopening FileMaker Pro or one or both database files will not affect how a looked up value setting functions unless doing so triggers a script set in file options to run when the file is opend or closed. So I find this description a bit puzzling.

                      On the other hand, a screen refresh issue, where the current layout fails to properly update can be "solved" in this fashion, though entering layout mode, then returning to browse is simpler and you can add a script that does one of the following:

                      commit Record

                      Commit Record
                      Refresh window

                      commit Record
                      Refresh window [flush cached join results]

                      If the last option is needed, then we need to take a closer look at the design of your layouts and relationships to figure out why it is needed as this one can really bog down screen refreshes when you have lots of records involved.

                      • 8. Re: lookup for a summary total field FM10
                        cules

                        The Job Summary represents a snap shot summary of a production run from the Production Report. The Production Report is the daily paperwork for the production run.

                        Both the Production Report and Job Summary are Numbers only Field Type.

                        With a looked up value field option, you do not have to "find" any records in any table. It relies on a relationship to match the current record to records in the related table. A summary field referenced in this fashion will return the total of all matching records in the related table. (In this case all records in the production summary with the same SalesOrder number as the current record in the Job Summary Sales Order table.)

                        I have to find Sales Order Number to get the look-up I need. If I don't do the find first, the look-up value that I get is the summary total, average, count, etc. of the found records. It does not just look-up the same Sales Order for the Summary field. There are also times that I need a Job Summary of a run. A sales Order can sometimes have multiple Runs.

                        I have also tried importing the found Production Report records to a Temporary table and look-up the Summary Field. The first time, I deleted all the records on a temporary table and import the found records from the Production Report. This works. The second time I do this, it looks-up the old value again. The table would have all the records I'm trying to get for the Job summary report. I won't have to do a find in order to get the Job summary of what I just need.

                        On the other hand, a screen refresh issue, where the current layout fails to properly update can be "solved" in this fashion, though entering layout mode, then returning to browse is simpler and you can add a script that does one of the following:

                        commit Record

                        Commit Record
                        Refresh window

                        commit Record
                        Refresh window [flush cached join results

                        I tried this on a script and the result was still the same. The first time it's good and the second time, the old data is looked-up again.

                        • 9. Re: lookup for a summary total field FM10
                          philmodjunk

                          Profound apologies here.

                          I went to set up a demo file to make sure we were on the same page and made a discovery.

                          When using the looked up value option, a reference to a summary field, unlike a reference to a summary field in a related table you might have anywhere else in the system, will look up  the total of all the records in the table instead of the total of all matching records. My next stop is to Report an Issue to write this up as a bug.

                          I Do have two work arounds that are simple to set up and I'll go ahead and post a download link to the demo file here so that you can look at the demo and see how they work.

                          Use an auto-enter calculation instead of the looked up value. This will copy the correct total from the summary field.

                          Don't use any lookup field at all. Add the summary field from Production Report directly to your Sales Order layout.

                          The demo file: http://www.4shared.com/file/uPqzfV2I/ProdRptJobSummDemo.html