1 2 Previous Next 20 Replies Latest reply on Oct 14, 2010 1:33 PM by philmodjunk

    Find Results - Finding fields in a table using relationships to update field content

    BenMartin

      Title

      Find Results - Finding fields in a table using relationships to update field content

      Post

      Hi,

       

      I have searched high and low for a solution to this problem but as of yet have not had any luck. I have highlighted the main problem in red below. I have a table, calm_items. This table contains amongst others, the following fields;

       

      _pk_product_code

      items_sold

      total_value

       

      There are around 100 products in this table with unique product codes. At the end of the month the user runs a report to give them the total number of items for each product sold on each given day of the month. This data is updated by a report that is imported from their transaction software. Each sale has a transaction record, an sale record, and line items. The line items are updated with the imported CSV file from the transaction software with their correct sale dates (as sales are not always transacated on the same day as they are inputted).

       

      Once the line items are updated with a date, they can be referenced from the calm_items table by the following relationship (ymd being year, month, day);

       

      calm_items::_pk_product_code = line_items::_fk_product_code

      and    

      calm_items::accounts_ymd = line_items::transaction_ymd

       

      I display this data in a list view with a header and footer. The footer contains summary fields for total items sold, total gross, total net and total VAT. Each line in the list displays the product name / code, total items sold for that product, and the gross, net and VAT totals for that product. These lines are calculated by the above relationship.

       

      My problem begins when I enter the date. For example, if I update the date on my report list to 01/09/2010 then all of the values calculate. Some items may not have sold at all that month so I do not want to display these in my report. To remove them I perform a find on all items that have sold a quantity >0. This then displays only items with a sold quantity of 1 or more. I then print this report as 01_09_2010.

       

      At this point I update the date to 02/09/2010. All of my values recalculate as expected, however when I perform a find for items sold >0 I get odd results. It won't display all items that were visible in the unfiltered list view, some records have 0 totals, and some of the records with >0 items sold may only show 9/10 of the total items sold. The find may find 10 records, however only 7 records should have been found. I have tested this thoroughly and it appears the find is finding records based on the previous date, 01/09/2010. It will show me the records from the previous dates find, but the values from the new dates find, which give me incorrect totals due to my summary fields calculating based on the found results.

       

      The only way I can resolve this is to open the database management, choose any field, open that fields definition, click OK and close the database management. This then fully recalculates all values and the find works until I update the date again. At the moment each month I have to repeat this process 30 times and am unable to automate the process due to these errors.

       

      I have attached an image of the find results. On the left is the unfiltered list, on the right is the found results. Can anyone please advise as to why this may be happening?

       

      Many thanks, Ben.

      report_find_results.jpg

        • 1. Re: Find Results - Finding fields in a table using relationships to update field content
          philmodjunk

          The text in your screen shot is too small to read. A few questions may shed some light on how your solution may be fixed:

          What field type are the "ymd" fields? Text or date? They should be of type date so formatting differences in the dates entered don't result in data mismatches.

          Is calm_items::accounts_ymd a global field so that you only need to change the date in one field to see the totals for your items?

          You might try this script step to see if it helps: Refresh Window [Flush cached join results] before your perform your find to limit your records to only quantities > 0.

          • 2. Re: Find Results - Finding fields in a table using relationships to update field content
            BenMartin

            Hi Phil,

             

            Thanks for your response. If you right click the screenshot and save it, you should be able to zoom in a bit. The values are irrelevant really, it is more the fact the find for >0 is displaying completely blank records. The blank records are actually found records from the previous find with the previous date, but as the values have recalculated some are blank. It is purely caching the found set.

             

            ymd is a date field. It is also a global field.

             

            I have tried the Refresh Window [Flush cached join results] and have had no joy. I have tried it again since reading your suggestion just to be sure, but still with no avail. Any further comments would be appreciated.

             

            Ben.

            • 3. Re: Find Results - Finding fields in a table using relationships to update field content
              philmodjunk

              I tested a demo file that replicates what you describe as your structure but it doesn't show the problems you report. Please download it and see if you can spot any differences in how it is set up.

              http://www.4shared.com/file/AW4e5dL4/InventoryTotalsTest.html

              • 4. Re: Find Results - Finding fields in a table using relationships to update field content
                rjlevesque

                Does he possibly need a "Show All Records" command slipped in there between actions?

                • 5. Re: Find Results - Finding fields in a table using relationships to update field content
                  philmodjunk

                  @rjlevesque

                  Not accoring to my tests with the demo file.

                  • 6. Re: Find Results - Finding fields in a table using relationships to update field content
                    BenMartin

                    Hi,

                     

                    Thanks again for the comments. I haven't had a chance to test the demo file properly yet, but it is nowhere near as complex as the actual database I am having problems with. Some of the fields between relationships are calculations, and the calculations occuring on the report run 4 levels deep.

                     

                    I will test this in the morning and post the results.

                     

                    Ben.

                    • 7. Re: Find Results - Finding fields in a table using relationships to update field content
                      philmodjunk

                      Please document the precise calculations, tables and relationships involved. You may want to upload an empty copy of your file to a file share site so that we can test it.

                      • 8. Re: Find Results - Finding fields in a table using relationships to update field content
                        BenMartin

                        Hi,

                         

                        The relationship between calm_items and accounts_line_items_by_date is defined as follows;

                        _pk_product_code = _fk_product_code     and

                        accounts_report_ymd = commidea_trans_ymd

                        The accounts_line_items_by_date table has it's records updated with a script that pulls in data from a CSV file. This performs some calculations and then sets fields accordingly. The values in the accounts_line_items_by_date records are static and do not change.

                         

                        calm_items::accounts_report_ymd is defined as follows;

                        accounts_report_day &"/" &

                        Case
                        (
                        accounts_report_month = "January" ; "01" ;

                        accounts_report_month = "February" ; "02" ;

                        accounts_report_month = "March" ; "03" ;

                        accounts_report_month = "April" ; "04" ;

                        accounts_report_month = "May" ; "05" ;

                        accounts_report_month = "June" ; "06" ;

                        accounts_report_month = "July" ; "07" ;

                        accounts_report_month = "August" ; "08" ;

                        accounts_report_month = "September" ; "09" ;

                        accounts_report_month = "October" ; "10" ;

                        accounts_report_month = "November" ; "11" ;

                        accounts_report_month = "December" ; "12" ;
                        )

                        &"/" & accounts_report_year

                        calm_items::accounts_report_day is manually defined.

                        calm_items::accounts_report_year is manually defined.

                        I have tried changing calm_items::acounts_report_ymd to a text field and manually entering the date incase the relationshi was not updating with a calculation field as the link, but this did not make any difference.

                        Within calm_items the products display the following fields in the report;

                        calm_items::_pk_pc_name

                        calm_items::accounting_date_total_sold - unstored, number, from calm_items, =

                        accounts_line_items_by_date::reports_total_sold

                        calm_items::accounting_date_total_cost - unstored, number, from calm_items, =

                        Sum ( accounts_line_items_by_date::item_qty_inclusive_cost )

                        calm_items::accounting_date_total_net - unstored, number, from calm_items, =

                        Sum ( accounts_line_items_by_date::item_net_cost_qty )

                        calm_items::accounting_date_total_vat - unstored, number, from calm_items, =

                        Sum ( accounts_line_items_by_date::item_qty_vat )

                         

                        The accounting_date_total_xxx fields are collecting data from accounts_line_items_by_date calculation fields. accounts_line_items_by_date::item_qty_vat for example is defined as follows;

                        item_vat * item_quantity

                        If you need any further information please me know.

                         

                        Ben.

                        • 9. Re: Find Results - Finding fields in a table using relationships to update field content
                          philmodjunk

                          Shouldn't calm_items::accounting_date_total_sold be defined as SUM ( accounts_line_items_by_date::reports_total_sold )?

                          or is this a summary field in the line items table?

                          and is this the field that you are specifying a greater than zero amount when performing a find?

                          I really don't see any other possible issues here. Your calculation for date could be simplified and only works with systems settings that are DDMMYYYY so I get the month and day reversed on my MMDDYYYY system, but it does work so I don't see where it's an issue.

                          • 10. Re: Find Results - Finding fields in a table using relationships to update field content
                            BenMartin

                            Hi Phil,

                             

                            Sorry for the slow reply. calm_items::accounting_date_total_sold is a summary field in the related line_items table.

                            This is one of the fields I am using for the >0 find yes.

                            The date calculation was setup like that because there is a day field, month field and year field where the date is written in it's word form as it is used elsewhere. I'm honestly not sure where the problem lies. Is there any further information I can provide that might help shed some light on it?

                             

                            Ben.

                            • 11. Re: Find Results - Finding fields in a table using relationships to update field content
                              philmodjunk

                              When I test my small demo file with changes made to match what you've described, it all works for me--including your date calculation. You might try saving a clone (empty copy) of your file and then importing all your data into the clone and testing the clone to see if you get the same results. This import into a clone method rebuilds all the indexes in the file and sometimes that clears up issues with a find or sort that isn't performing as expected.

                              If you have FileMaker 11, you can also rebuild all your file's indexes with the recover command:

                              1. Select advanced options
                              2. Specify only "Copy Blocks as is" and "rebuild field indexes | Now"

                               

                              I understood why you have the calculation for date, it just assumes a DDMMYYYY date format so it will fail for any user that uses different date system settings. It should work for you just fine.

                              Here's an alternative date calculation that doesn't limit you to a specific system setting:

                              Date ( Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( Accounts_Report_Month ; 3 ) ; 1 ; 1 ) / 3 ) ; 
                                         Accounts_report_Day ; 
                                         Accounts_Report_Year 
                                        )

                              • 12. Re: Find Results - Finding fields in a table using relationships to update field content
                                BenMartin

                                Phil, if I could kiss you through the internet I would!

                                 

                                The recover with rebuild field indexes fixed it instantly! Thank you very much. After months of head scratching and having to manually run this customers end of month reports I can finally tell them that they can run the reports themselves! I tried a recover months ago before I had FM 11 at my end and it didn't fix it, but doing the rebuild in my copy of FM11 seemed to resolve it instantly. I shall bear this in mind in the future.

                                 

                                Thanks again, Ben.

                                • 13. Re: Find Results - Finding fields in a table using relationships to update field content
                                  BenMartin

                                  Hi Phil,

                                   

                                  Sadly I must retract my kiss as the problem persists! The find now works on the server where the file is hosted, however does not work on any of the client machines. The problem is excatly the same as before on all of the clients, the find still finds previous records with the new values. Any idea as to why this may be? I have tried this on both Filemaker server and client with sharing.

                                   

                                  Ben.

                                  • 14. Re: Find Results - Finding fields in a table using relationships to update field content
                                    philmodjunk

                                    Please spell out what's different between "does not work on any of the client machines" and "now works on the server where the file is hosted". That sounds like you have more than once copy of the file unless you mean "works on the server" is opening a local file and "does not work on any of the client..." means you host the file and open it remotely from the client.

                                    There have been cases where FileMaker behaves differently when hosted. I want to be sure I understand the differences here so I can try to replicate the issues.

                                    1 2 Previous Next