4 Replies Latest reply on May 1, 2012 9:20 AM by AllanAlbrecht

    Update container via related table

    AllanAlbrecht

      Title

      Update container via related table

      Post

      This is my first post and I stand up proud as a newbie to FM. My past experience has been Dbase, FoxPro, and others.

      1. Two tables: Job table which includes a status field (text) and others. Status lookup table includes status (text) and container (graphic). The graphic is simply a colored square (a visual indicator for form layout).

      2. The two tables are related via the status field.

      CONCEPT

      The user is editing the Job details in a form. He selects the desired status via a dropdown list. When that status value is changed, an indicator next to the Job title on the form changes to give the user a quick visual clue as to the jobs status (green, orange, red).

      PROBLEM

      I set up the form to display the jobs table and the dropdown for the status field, that works (straight forward).

      But how do I have it update the indicator (immedaitely...prior to forced save)?

      I thought I would have to bring the container field from the lookup table onto the form, but can't find how to do that.

      I really don't want to create another field in the job for a container as that would be redundant data on the record.

      I also thought I could do a calculated container field on the job table , but can't figure out how to get it to update automatically when the user changes the status value.

       

      Any help would be appriecated!

      Allan

        • 1. Re: Update container via related table
          philmodjunk

          I thought I would have to bring the container field from the lookup table onto the form, but can't find how to do that.

          This can be done and is your best option here. Have you defined a relationship between Job and Status Lookup?

          If you link your tables like this:

          Job::Status = Status Lookup::Status

          Then you can use the field tool to add the container field from Status Lookup to your Job layout. This tool opens a specify field layout where you can select Status Lookup from a drop down and then you can click the container field to select the container field for you new field.

          I also thought I could do a calculated container field on the job table

          This is not a necessary complication here, but if you define a field of type calculation, you can set up a calculation that refers to container fields and you can specify container as the calculation's return type. (This is a drop down in the lower left corner of the dialog box.)

          • 2. Re: Update container via related table
            AllanAlbrecht

            Thanks Phil,

            Your first solution is the same one I came up with, but when it was all set up I tried it.....and failed. <heavy sigh>

            In the layout I was easily able to edit the Job status with a dropdown list containing the 3 status options (lookup table). So I know the realtionship is correct and working.

            I also had the status container (lookup table) on the layout, but when I changed the job status, the container just sat there and did nothing no changes. 

            I was leaning towards putting in the calc field, but in my mind that should not be necessary. When the job status changes, the container (attached to job status lookup table) should change to reflect the new record pointed to in the relationship.

            I know it is my newbieness that is blocking me here. Thanks for your help.

            Allan

            P.S. I did get another method to work using a container field on the actual job table, and a script firing when the job status changes. Though this works, it is NOT the right way to do it. I just allowed me to continue developing the project until I got the CORRECT method in place.

            • 3. Re: Update container via related table
              philmodjunk

              In the layout I was easily able to edit the Job status with a dropdown list containing the 3 status options (lookup table). So I know the realtionship is correct and working.

              The correct function of your value list really has nothing to do with whether or not you have any relationship at all linking your two tables.

              We'll need to see what relationship you've defined and make sure to use the correct field for your value list of status options.

              If you have this relationship:

              Jobs::Status = Job Status Lookup::Status

              It's the Jobs::Status field that should be set up with the drop down list of status values. Also, if you are selecting text in your value list, make sure that both status fields have been defined as type text, not number.

              • 4. Re: Update container via related table
                AllanAlbrecht

                Thanks for walking me thru this Phil.

                As you might've seen I have another post on here questioning if FM is the right tool for me. Probably you'll be the guy to reply (haha).

                Until then, I'm gonna just pause and research XCode a little further. I don't want to expend a lot of energy learning FM just to run into a dead end down the road.

                Thanks again for your help.

                 

                P.S. Yes, I had the relationship correct all along.