14 Replies Latest reply on Feb 21, 2014 6:03 AM by AjEGfmTech

    Compare Portal Records

    AjEGfmTech

      Title

      Compare Portal Records

      Post

           I have a layout with two related, filtered portals. I would like to conditionally format the fields in one of the portals based on a comparison between the two records of the filter. The problem is, I don't know how to target the field in portal 1 from the conditional formatting of the field in portal 2.

           Example.

           Two tables, 'Stores' and 'Prices', one-to-many (each store can have many price lists).

           Layout is in context of Stores and shows a Store record.

           Portal 1: from Prices table, filtered by PriceStatus = "previous" and shows several price fields.

           Portal 2: from Prices table, filtered by PriceStatus = "current" and shows several price fields.

           I would like to conditionally format the price field in 'Portal 2' if the price does not equal the same price field in 'Portal 1'. I have been playing with the calculation in the conditional formatting, but I cannot figure out how to target the specific record in 'Portal 1'.

           I had tried...

           Prices::PriceStatus = "previous" and Self ≠ Self

           AND

           Prices::PriceStatus = "previous" and Prices::Item_Sandwich ≠ Self

           Neither of these have worked. This seems like it should be pretty straight forward, but I haven't been able to figure it out. Does anyone have an idea?

            

        • 1. Re: Compare Portal Records
          philmodjunk

               So if I have 3 records in portal one  and 3 records in portal two. Which record in portal one has the data to be used to compare to a record in Portal two?

               Are they prices for the same product at the same store?

          • 2. Re: Compare Portal Records
            AjEGfmTech

                 Thanks Phil. I'm sorry, I probably explained this poorly. There is only one record per portal (based on the filter), but many fields for that record (item prices). I'm basically just comparing existing prices to new/proposed prices and showing what prices have changed. Each store may have about 30-40 items to price, so it becomes difficult for the POS programmer to pick out what has changed in a new price list.

                 So, to clarify. One filtered record per portal (existing prices in Portal1 and new/proposed prices in Portal2) and the prices in the portals are for the same product fields at the same store.

                 I hope that helps and thanks for your help.

            • 3. Re: Compare Portal Records
              philmodjunk

                   If you take these portal filter expressions:

                   Portal 1: from Prices table, filtered by PriceStatus = "previous" and shows several price fields.

                   Portal 2: from Prices table, filtered by PriceStatus = "current" and shows several price fields.

                   Remove them and replace them with "filtering" at the relationship level, your conditional format expression can then specifically refer to the same record shown in the filtered portal you now use.

                   Example for portal 1. Modify the relationship to be:

                   Current match fields here AND
                   ParentTable::constPrevious = PortalTable::PriceStatus

                   Define constPrevious as a calculation field with text as the result type and "Previous" as its calculation.

                   You'll need two Tutorial: What are Table Occurrences? of your portal table instead of one so that you can specify different sets of match fields for each.

                   The alternative is to use ExecuteSQL in your conditional format calculation with a WHERE clause that replicates the portal filter to match to the price field of the correct record in your portal's table.

              • 4. Re: Compare Portal Records
                AjEGfmTech

                     Thanks for the describing the relationship filter. Unfortunately, I have not been able to get it to work successfully.

                     ParentTable is 'Stores'. PortalTable is 'Prices'.

                     In 'Stores' I created two new calc fields.

                     constPrevious which has a very simple calculation, "Previous".

                     constCurrent which is also simple, just "Current".

                     The ParentTable (Stores) has three TO's related to it.

                     'Stores' to 'Prices' (using Stores::kp_StoreID = Prices::kf_StoreID)

                     'Stores' to 'PricesPrevious' (using using Stores::kp_StoreID = Prices::kf_StoreID AND Stores::constPrevious = Prices::PriceStatus)

                     'Stores' to 'PricesCurrent' (using using Stores::kp_StoreID = Prices::kf_StoreID AND Stores::constCurrent = Prices::PriceStatus)

                     On the 'PriceCompare' layout that has 'Stores' as the context table, I created two portals. Portal1 shows records from 'PricesPrevious', Portal2 shows records from 'PricesCurrent'.

                     I had created three price records of unique prices for a single store in the 'Prices' table and set their 'PriceStatus' field to: Archived, Previous and Current, respectively. I then went to the 'PriceCompare' layout to see the result. Both portals showed the 'Archived' prices, the first price record.

                     I changed the PriceStatus on the price records, but it made no difference in what was displayed in the portals. They always showed the first record. I even opened up two windows so I could see the 'Prices' layout and the 'PriceCompare' layout simultaneously. I modified the 'PriceStatus' field in the three price records to see what effect it would have on the portals. No matter what I changed the PriceStatus to in the first record, the portals always showed the first record prices. Strangely though, the second and third records had to be set to either 'Previous' or 'Current'. It didn't matter which was set to which, but if the first record was set to 'Archived', one of the other records would have to be set to 'Previous' for the Previous portal to populate and one would have to be set to 'Current' for the Current portal to populate. In either case, both portals populated with the first record's prices, the one that was set to 'Archived'.

                     Obviously, I have something incorrect and clearly don't understand well how the filtered relationship is supposed to work. What you explained made sense. Create two records in the Stores table that always have a 'constant' value of the status I want to display (Previous and Current). Relate a TO of the Prices table to the Stores table using the StoreID and the PriceStatus constant as match fields. This would make me think that the portal showing the Previous TO would only show the record that matches the StoreID AND the PriceStatus constant. But that's not what resulted.

                     Maybe this is due to their being multiple price records for a single store, and therefore it always shows the first record data? Any help or clarification would be greatly appreciated. Thanks for your time.

                • 5. Re: Compare Portal Records
                  philmodjunk
                       

                            They always showed the first record.

                       There's a fairly common layout design error that can produce that result. Make sure that the field added to your portal is from the same table occurrence specified for that portal. If it's from a different table occurrence of the same table, the different relationship kicks in and you get the same "first related record" in each portal.

                       Also make sure to remove the portal filtering as it is no longer needed.

                        

                  • 6. Re: Compare Portal Records
                    AjEGfmTech

                         Thanks Phil! That worked! When making the changes I had not gone back to the fields in the portals and changed the TO to one of the new (Previous or Current) TO's, they were still set on the 'Prices' table. That did it. Thank you very much.

                         I was also able to then get the conditional formatting to work by using the calculation...

                         stores_PRICES__Current::Item_Sandwich ≠ stores_PRICES__Previous::Item_Sandwich

                         Since I will have about 30-40 price fields per store to do this comparison with, I was wondering if there might be a shorter way to setup the conditional formatting calculation, possibly something involving 'Self' ? I'm just thinking that the comparison will always be between the same field in two records, it would be nice if I didn't have to name that field in every conditional formatting calculation and could instead refer to it in a relative way, like 'Self'. Maybe that's not possible or really complicated to avoid, but I thought I would ask just in case.

                         Thanks again Phil.

                    • 7. Re: Compare Portal Records
                      philmodjunk

                           Hmmm, but maybe those 30-40 price fields are actually 30-40 records? and thus a conditional format need only be set up once? This could easily be a list view of 30-40 records as I see it. (But your field name: item_sandwich, suggests a serious design issue with your data model if you are using different fields in the same record for different products...)

                           but if the field being conditionally formatted is either of the two fields used in the conditional format calculation, then you can use self in place of that field reference.

                      • 8. Re: Compare Portal Records
                        AjEGfmTech

                             That is an interesting idea, one that I haven't thought of before. I am a novice-intermediate user. I've used FM for many years, but always at a low level. I am trying to 'up my game' with regards to the databases we use in our firm and FM13 is providing additional incentive due to WebDirect and iOS.

                             I am in the process of rebuilding what I call the 'Store Details' database and these questions are the result of some prototype testing I'm doing. I had considered setting up the PriceStatus as a separate table so that I could log the history of each price list by allowing multiple status changes each with their own timestamp. In the real database the statuses are Proposed, Returned (for revision if not approved), Approved, Published and Archived. I just used the Current and Previous to help keep the forum post simpler.

                             As I said, I hadn't considered moving the products themselves into a separate table, but as I think about that, it makes sense and would seem to have some advantages in terms of products that get added/removed during the year. I like to keep things as simple as possible, but that idea may be simpler in the long run.

                             As you hinted, yes, the current database does use different fields in the same record for different products. From what I gather in your response, that is not a good database design?

                        • 9. Re: Compare Portal Records
                          philmodjunk

                               It's a database design that will be extremely cumbersome to work with. You really need a table where you have one record for each product and a field in that record that serves as the primary key--usually set up as an auto-entered serial number--so that you can use that ID field to link product records to other tables.

                          • 10. Re: Compare Portal Records
                            AjEGfmTech

                                 I would agree with the cumbersome part. Thanks for the suggestion, it gives me a lot to think about.

                                 Would having products in a separate table as records instead of the same table as fields make it more difficult to create calculation fields that are currently based on specific product fields?

                                 Example

                                 Currently I have calc fields to calc bundled items: ie. Box Lunch = Sandwich + Chips + Pickle + Cookie. If each of those items were records instead of fields, would that be more difficult/complicated to target those in the Box Lunch calc field?

                            • 11. Re: Compare Portal Records
                              philmodjunk

                                   It is an added complication but the end result--using different records for each, is much more flexible.

                                   A number of people here in the forum have encountered this issue as a "kit" or "package deal" where you want to make single product selection but list all the components that make up that item as separate line items on their invoice.

                                   What I've advised in that case is to include a script run from a script trigger that checks to see if the selected item represents such a "kit" or "package deal" and it then adds in the needed components automatically.

                                   The key is an added table occurrence of products that sets up a many to many self join between them to list all the products that make up such a "kit":

                                   Products-----<Product_Component>------Products|Components

                                   Products::__pkProductID = Product_Component::_fkKitProductID
                                   Products|Components::__pkProductID = Product_Component_fkCompProductID

                                   An additional field in Product_component is used to record the qty for that  component. (So if your sandwich uses two pickles, you'd put a 2 in this field.)

                                   You'd use a portal to Product_Component on your Products layout to list all the product components that make up that item. The above mentioned script would pull up that list of Product_Component quantities and product IDs and loop through them to create your additional line items. The script would multiple the Qty field from Product_Component with the Qty field for the "kit" to compute a qty for the line item entry. (If you sell 2 sandwiches, your Qty for Pickles will be 2 X 2 = 4.)

                              • 12. Re: Compare Portal Records
                                AjEGfmTech

                                     Thanks for the thorough explanation Phil. I see where the flexibility of the products table could become helpful. The bundled products does indeed seem to add complexity, but a good understanding of relationships should help that to a great degree. That is the area I need to focus my attention on in terms of increasing my understanding of FM and databases in general - how relationships and TO's can be manipulated to get various results.

                                     Thanks again Phil. I greatly appreciate your time and input.

                                • 13. Re: Compare Portal Records
                                  philmodjunk

                                       You may find this tutorial on table occurrences helpful if you have not already seen it: Tutorial: What are Table Occurrences?

                                  • 14. Re: Compare Portal Records
                                    AjEGfmTech

                                         Thanks Phil. No, I had not seen it before and yes, it was helpful. Thanks. I know I need more knowledge and experience in the subject of relationships and what possibilities that can open up. Thanks again Phil.