My issue is a bit hard to explain properly, and I'll simplify the logic a bit:
- I load 2 tables, with 1 key field in both called "Material".
- I then create a pivot table with the field "Material" and then values from table 1 in one column and different values from table 2 in a second column. I also create a filter field, with values ("Field 2") from table 1; this field doesn't exist in table 2!
- then to use the pivot report, the user would select just one value in filter "Field 2" from table 1.
The report then shows only the values for materials that are present in table 1, because that is where the filter field exists. But it doesn't show values/Materials if they exist only in table 2. My issue is, that I would still like to show all the Materials from both tables.
My solution was to take all the "Field 2" values from table 1, and Join them to table 2. That way they exist in both tables. The solution works, but in a very short time the amount of records that i'm loading and saving will grow by alot, and I fear the report performance will be much worse (from a rough calculation I would go from about 50.000 records for 2 weeks, to about 50.000.000 records for a year
Any ideas how I can avoid so much data as a result of the Join?
