1 2 Previous Next 26 Replies Latest reply on Apr 4, 2015 6:55 AM by DustFairy

    Related Value List

    DustFairy

      Title

      Related Value List

      Post

      I have a parent-child relationship:     Unit-------<Inspection.

      A Unit gets inspected each year and then is due for inspection one year later.

      I have been trying several ways to get the Inspection Due Date field in Unit to update with each new inspection.

      I tried a List function to list the inspection dates and then a Right values to select the last date.  Thought it worked until I realized all unit due dates were updated to the date of the last unit inspected.

      The Inspection record has an auto-fill current date field but if I use that field (as expected) I just get the first record date, when what I want is the last record date.

      Does anyone have any suggestions on what I should try?

      Thanks in advance.

        • 1. Re: Related Value List
          philmodjunk

          There's a simple function to do what you want, but I'm concerned by your report that using List didn't work. Defining:

          RightValues ( List ( Inspection::InspectionDate ) ; 1 ) as a calculation in the Unit table,

          should have returned the correct date.

          So should Last ( Inspection::InspectionDate ) or even Max ( Inspection::InspectionDate )

          so if you still can't get this to work, put an unfiltered, unsorted portal to inspections on your unit layout. both the Last and RightValues methods should return the date from the record found in the last row of this portal. Max will return the largest (latest) value of all those listed in the portal.

          • 2. Re: Related Value List
            DustFairy

            List sort of works but seems to update all units to the date of the last unit inspected.  At least I now know that I'm not completely off base and can put a little more effort into finding my problem or using one of your other suggestions.

            • 3. Re: Related Value List
              philmodjunk

              List sort of works but seems to update all units to the date of the last unit inspected.

              And as I said before, this should not be the case. Something isn't set up correctly. It sounds like you have a relationship that matches to all inspection records instead of just those for the current unit record. Perhaps you have used the X operator to match to all records in inspections but then used a portal filter to limit the records to those for the current unit. This would match what you describe and this is why I suggested using an unfiltered and unsorted portal to check and see what records are being matched up by your relationship.

              If this is the case, you need to change your relationship to not use the X operator.

              Unit::__pkUnitID = Inspections::_fkUnitID

              is a relationship that needs no portal filter and which should work to support what you need here--including but not limited to accessing the most recent inspection date.

              • 4. Re: Related Value List
                DustFairy

                I think I have found the problem.  

                My relationship is:

                Company----<Unit---<Inspection

                Inspection numbers are actually a field from the company table that increases by 1 for each new inspection for that company.  When I List the inspection numbers I get the full company list.  I imagine the solution is to List the inspection numbers and then filter by the unit number, then Right value the filtered list?

                • 5. Re: Related Value List
                  philmodjunk

                  The relationships that you show should work just fine IF you have set up the correct match field values for your relationship and set up this calculation to evaluate from the Unit table and not the Company table. A field from the company table that increases by one for each inspection is NOT the match field to use to link a unit record to its inspections. You'd use a field in Unit that uniquely identifies each unit.

                  • 6. Re: Related Value List
                    DustFairy

                    I think I have done what you are suggesting.  __pkUnit ID is matched to _fk Unit ID on the Inspection.  I populate the _fkUnitID with values from a Unit value list.

                    Screen shot attached and a couple to follow.

                    • 7. Re: Related Value List
                      DustFairy

                      Second screen shot

                      • 8. Re: Related Value List
                        DustFairy

                        Final screen shot.

                        • 9. Re: Related Value List
                          philmodjunk

                          Your calculation evaluates with a context that refers to the wrong occurrence of unit. The Inspection table occurrence is linked to company and not  to Unit.

                          Your list function should specify Unit 3 in the context drop down at the top of the Specify Calculation dialog. This is because it is the relationship from Unit 3 to Inspection that actually matches records by unit ID. There is not such relationship between unit and Inspection.

                          BTW, I see no purpose to linking inspection directly to company.

                          • 10. Re: Related Value List
                            DustFairy

                            Thanks.  That worked.

                            I alway thought my relationship should be:

                            Company----<Unit----<Inspection   but for some reason it didn't work quite right.  (So long ago I can't remember what the problem was)

                            I'd like my data base to be built properly so might go back to that if you agree it should work that way.

                             

                            • 11. Re: Related Value List
                              DustFairy

                              Getting back to your comment about no purpose linking inspection directly to company.  I think I did it because I had problems seeing the Units owned by the company when I was starting a new inspection.  

                              I am having a similar problem now with another three tables.   I started with ( Mach. Category------<Mach. Type-----<Unit )   When I was on a Unit based  layout and wanted to see the various Mach.Type but only the related values from Mach. Category I get a "No Defined Values" message.   I finally switched to the relationship    Mach.Type>-----Mach.Category-----<Unit and now when I am on the Unit based layout I can see the Category related Type's.   Screen shot attached.

                              Does that make sense?  I would have thought the related values should work thru the Category-----<Type----<Unit relationship.

                              • 12. Re: Related Value List
                                philmodjunk

                                Your relationships functioned exactly as you designed them to do, just not as you expected them to do.

                                When you have these one to many relationships:Mach. Category------<Mach. Type-----<Unit

                                When you are on unit, there can only be (at most) one related record in Mach. Type and at most one related record in Mach. Category.

                                So "I wanted to see the various Mach.Type but only the related values from Mach. Category" isn't going to work they way you wanted. If you were on a Unit layout and created a new record, there would, by definition be NO related records in Mach. Category as the new record would not yet link to any records in that table. Once you entered an ID linking the new Unit record to a Mach. Type record, you would then have exactly one Mach. Category record linked to it (or no records linked from that table if the related Mach. Type record isn't so linked.)

                                But keep in mind that this is not an "only one or the other" set of options. You can set up both sets of relationships using different occurrences of the same table to get one set of relationships such that you can select a category and see all related Mach. Type records and yet also have a layout based on Mach. Categories that lets you see either a list of all related Mach. Type records and/or a list of all related Units for that category in portals placed on that layout.

                                 

                                • 13. Re: Related Value List
                                  DustFairy

                                  It sounds like I have been thinking the relationship worked in the reverse order to what it really does.  Seems you have to start at the one and work toward the many.  I have also been trying to minimize multiple table occurrences thinking it would make for a sloppy disorganized database.

                                  Once again thanks for your help and patience as I struggle thru this process.

                                  • 14. Re: Related Value List
                                    philmodjunk

                                    It's quite possible to start from the many and reference the one, but first make sure that your current "many" record is correctly linked to a record in the "one" table.

                                    1 2 Previous Next