Can you explain how, "on paper" this is supposed to work for the end user?
Is this supposed to be set up so that the user selects several different "zones" and the product from each zone is compared? Or does the use select 1 zone and all products from that zone are compared to each other? Or ???
each zone has a varying quantity of one existing product type. The zone has a number of variables for example hours of use and calculations such as running costs. The system is designed to show the comparison of a new, suggetsed product against the existing product in that enviroment. Each new instance of the existing product is a new zone
you'll appreciate, its taken awhile to find the right phrasing for the question. sorry if its not clear but i am into confidetiality isues with the what the product is.
It's still not clear.
Are you comparing the same product from different zones or different products in the same zone?
You have both different zones and different productID's involved and I don't know how they affect the methods you need to employ to pull up two or more records for "comparison" nor what kind of comparison that might be.
This might be as simple as entering two product ID's into two rows of the same portal in order to see the same data side by side. And calculation fields can be set up a number of ways to further manipulated the data in a set of such related records so as to "compare" them.
First of all, thanks for your patience. Databases aside:
An office block has rooms (zones). Each room has one specific widget (item). This widget is a product with a specification and a cost. It has a set of variables such as running time that are locked to the zone(room) and these will vary from room to room. Over time specification items such cost will also change.
We propose to substitute the existing widget for a better widget. The specification and cost of the alternative will change favourably and we need to demonstarte that. The report for each widget/substitution is a 3-page scientific document.
I would create a new zone if two widgets were in one room
Ideal is one form with three columns. Left column is existing product. Middle column is new product. The fields for each of these columns are identical in every way. I'm attaching a screenshot from the v1 database where all fileds are in a single table
What I read from that on zones and product IDs is that, for the purposes of this question, a zone and a product ID are two ways to identify the same product since you only have one product in each zone. This appears to conflict with the screen shot of your relationships as it shows two occurrences linked to the same ZoneID field. I would think you need to look at productID's that have different zoneID's from what I have just read.
I'm attaching a screenshot from the v1 database where all fileds are in a single table
Techinically, they are still in a single table with your proposed design change. You just have a new Occurrence of that relationship that links to the same data source table as before.
To display the data in two columns like this, I'd set up two one row portals side by side. A portal row's height can be increased so that you can vertically arrange the fields as shown.
You can do it one of two ways:
1) Use filtered portals with a filter expression that selects the specific ZoneID or ProductID for the data to be displayed. The relationship for both portals might be:
zones::anyfield X ComparisonItem::anyfield
and fields, possibly global, are used to specify the product or zone ID for each portal so you end up with expressions in the portal filter such as:
first Portal: zones::ZoneID1 = ComparisonItem::ZoneID
second portal: zones::ZoneID2 = ComparisonItem::ZoneId
This is good for side by side display and keeps your relationship graph simpler, but is only good for display purposes. If you need to refer to data from both item records in a calculation try the second option.
2) Use the two occurrences like you have, but use a different field for the ZoneID on the zones side of the relationship. This does not require a portal filter, nor even a portal and now calculations can refer to data in both item records in the same expression, but you now have to add the extra table occurrences to get this to work.
So straight off the bat - Thank you, I love you and want to have your babies.
Option 2 was the selected solution and has resolved the primary issue. The solution now looks like the attached
The next phase is to move common fields from the item table to a more suitable location. For example when comparing like for like, the quantity should not change. Therefore product quantity should move to the zone table.
This common quantity field should be used in calculations for the existing item value and the replacement item value. Problem is that the calculation in the items table can only calculate in the context of one of those tables. Any suggestions?
As I understand it, there is a different zone record for every item.
Context will not matter as long as it matches to the correct zone record via the relationship specified by the "context" table occurrence.
But this does suggest that you may need to reconsider this set of relationships.
I don't think your comparisons should be made from the context of a zones layout since you are also specifying two different zones. With the current setup, you are comparing records from the context of a specific zone record while pulling in item data for a completly different zone record in order to compare them.
You may want to use a completely new table with the two specified "Zone ID" fields for this so that you keep one and only one zone ID field in zones.
Also, if you rearranged your layout design for comparing items, you could use a list view layout based on zones pull up the zone records for the two items and display them one above the other instead of side by side. This would enable you to compare item records without using any additional occurrences and relationships.