I'm hoping someone can help. I have an application with many tables which I've built my own relationship table to handle all the table joins.
I've now got an issue where there is an unintended relationship between two tables which is giving me an incorrect result.
I've attached a small simple test application that should describe the issue (I've also attached the Excel doc with the raw data if that helps on changing the load script).
The issue is as follows..
I have a table called 'XRef' which has a cross reference of Items to a customer item In the example attached, ItemID = 1 is currently referenced to customer item 'xa'. Now in the past, customer item 'xa' was referenced to ItemID = 2 and you'll see in the sales table it has a sale of item xa (XrefID = 1) to ItemID = 2, I do want to show this in sales s it was a sale of 'xa', so in my straight table the total sales quantity sold of 30 of Customer Item 'xa' is correct as I do want to view the entire history of sales of 'xa'.
The stock information should only point to the stock of the ItemID which is currently mapped to item 'xa' in the XRef table, hence as it's currently mapped to ItemID = 1, stock is 0 so it should show 0 stock, however it's picking up the stock onItemID = 2 too which is not what I want.
I've intentionally used a relationship table in order to force the joins to the data I require but there's still an unintentional join to ItemID = 2 via the Sales relationship.
My goal is a table showing the following:
CustomerItem
ItemName
sum(Qty)
sum(Stock)
xa
ItemA
30
0
I thought of using two seperate identical Item tables, however I use other fields in the Items table in my app as selection boxes e.g. Manufacturer, Barcode, Colour etc, if I have two item tables then a selection of Manufacturer from the original Item table will not filter by Manufcaturer in this example as it would be working off an independent Item table.
Ideally I want it to work off the current Item Table. If anyone has any ideas of how I can make it look at all sales, but only item data from it's current cross reference to an item I would be most grateful.
Hi Derek In order to accomplish what you need, I will add which one is actve and which one is not so you can do Sum(Qty) and it will get active and non active = 30 Sum({<Active={1}>}Stock) will get the current item stock = 5
Sum({<Active={0}>}Stock) if you want to see stock of old items = 0
It however still does not solve my issue around restricting the item dimensions to what 'xa' is currently assigned to e.g. 'xa' is currently colour of red as it's mapped to ItemA, so if I had a listbox with colour and I were to select 'red' I would want 'xa' to show with Sales of 30 and stock of 5 still. However this is not possible still as there is still this unintended join to items from sales, so sales will drop to 20 if I selected red which is not what I desire.
Getting closer to my goal thankfully. I'm thinking maybe I need to create a secondary sales table with xref data only (so no join to Items), then I can use this to get out all sales on xrefID = 1, whilst still only joining to the Items table by it's current item mapping. My only concern is my actual real life sales table is the largest table in my app at around 4m rows, so duplicating this is my last resort.
Hi Derek In order to do that you need to do Sum({1}Qty) it will disregard the color selection in the total but in the one that has it by colors it still gives you a good result
I see what you are doing, your result allows sales qty to be 30 with 'red' selected, however if I'm trying to report colour in my table, it still splits it up i.e. in your qvw the table looks like this:
