Find Results - Finding fields in a table using relationships to update field content
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;
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
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.