9 Replies Latest reply on Jul 17, 2012 1:06 PM by philmodjunk

    Table Occurrences

    obidon

      Title

      Table Occurrences

      Post

      Morning

      I have an item table which calls data from a product table via a lookup and then performs a series of calculations based on a quantity.

      The primary function of my database is to compare 2 items. The client wants a side by side comparison with compare and contracts calculations. The result is a form with three columns.  There are approx 15 rows in the final form.

      In my early versions of this solution, I simply created an existing product ID and a set of existing product fields.  I then created a 'new product ID and duplicated all of these fields within the same table, modifying as necassary.  The result is 30+ fields.  It ain't pretty but it worked.

      In version 2 of the database, the number of fields per product will increase to 50+.  I believe that the correct procedure is to create one table and use multiple occurences.  But after days of chasing my tail, I just can't get the relationships figured out and I am causing issues with my calcs

       

      Help!

      Screen_Shot_2012-07-17_at_12.24.37.png

        • 1. Re: Table Occurrences
          philmodjunk

          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 ???

          • 2. Re: Table Occurrences
            obidon

            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.

            • 3. Re: Table Occurrences
              philmodjunk

              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.

              • 4. Re: Table Occurrences
                obidon

                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

                • 5. Re: Table Occurrences
                  obidon

                  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

                  • 6. Re: Table Occurrences
                    philmodjunk

                    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.

                    • 7. Re: Table Occurrences
                      obidon

                      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

                      • 8. Re: Table Occurrences
                        obidon

                        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?

                        • 9. Re: Table Occurrences
                          philmodjunk

                          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.