6 Replies Latest reply on Dec 11, 2016 2:05 PM by erolst

    Set field according to matching fields

    scheip22

      Hi guys,

       

      Could anyone help me with following problem please. I think it is not that difficult, but I don't see it.

       

      I've two lay-outs:

      - AMP: Maintenance tasks (table AMP)

      - HT: component specification (table HT)

       

      fields table AMP:

      - tasknumber

      - Interval

      - task description

       

      fields table HT:

      - component

      - installation date

      - Interval

      - tasknumber

       

       

      Lay-out AMP, summarises all maintenance tasks of a machine.

      All maintenance tasks have a different tasknumber (AMP::tasknumber) with a defined Interval (AMP::Interval)

       

      The other lay-out visualises all components of the machine. By importing a component the user has to type the related tasknumber for this component. By adding this tasknumber, the Interval have to be set automatically according to the predefined maintenance tasks in the maintenance  summary.

       

      HT::Interval = If HT::tasknumber equals AMP::tasknumber; get AMP::interval of the record that contains the defined AMP::tasknumber (which is equal to HT::tasknumber).

       

      How could I do this with a calculation/script?

       

      Many Thanks!

        • 1. Re: Set field according to matching fields
          erolst

          Assuming that you have a relationship between the two tables matching on taskNumber, add an auto-enter calculation to HT::interval that simply refers to (is) AMP::interval.

           

          scheip22 wrote:

           

          HT::Interval = If HT::tasknumber equals AMP::tasknumber; get AMP::interval of the record that contains the defined AMP::tasknumber (which is equal to HT::tasknumber).

          The part in bold is already encoded in the relationship definition between AMP and HT.

          • 2. Re: Set field according to matching fields
            David Moyer

            Hi,

            once HT::interval inherits the value of AMP::interval, is it allowed to change?  Or is it always tied to AMP?

            I'm wondering if you need two static fields with the same value.

            • 3. Re: Set field according to matching fields
              scheip22

              Hi David, It is always tied to AMP.

              • 4. Re: Set field according to matching fields
                scheip22

                Hi Erolst, I tried this before, but it didn't work.

                • 5. Re: Set field according to matching fields
                  David Moyer

                  It is always tied to AMP.

                  ... then you should either eliminate the HT::interval field, or change it into a calculation that is equal to AMP::interval.  You don't want to commit a database sin and repeat data in static fields.

                  If you choose to eliminate the second field, you can still view the AMP::interval from the HT context via the relationship you have set up (based on tasknumber).

                  • 6. Re: Set field according to matching fields
                    erolst

                    scheip22 wrote:

                    I tried this before, but it didn't work.

                    Do you have the appropriate relationship in place? Also note that ...

                     

                    - you need to uncheck the "Do not replace existing values ..." option for the auto-center calc if you're testing this with existing records that already have values

                    - choosing that option will not change already existing records; that must be done manually.

                     

                    Also- adding to David's post - be aware that you only need the same field in both tables if you want to capture a current value, so that later changes in the interval of the matching AMP record do not change existing interval values for HT child records. If AMP never changes, you can in HT simply display (on the layout) and reference (in calculations) the value from the matching AMP record.

                     

                    Last but not least perform a sanity check: make sure there is an AMP record with the taskNumber entered in HT, and that it has an interval value ...

                     

                    You can test your relationship by placing the related AMP::taskNumber field on the HT layout; if you enter a good value in HT::taskNumber, you must (by definition) see the same value in that field.