7 Replies Latest reply on Mar 19, 2015 9:20 PM by philmodjunk

    Get Value of Field from a related table from a portal row

    ToddNolan

      Title

      Get Value of Field from a related table from a portal row

      Post

      I have a layout from MainTable where I choose an age from a field and it shows a premium for that age for about 10 different companies. All these tables are related to the main table by age. My problem is:

      I want to be able to click on company A, B, or C .... premium field (inside the portal) and save it to a field called box1 in MainTable. I've tried the Get(ActiveFieldContents) funtion but it give MainTable::box1 a blank value.

      I know I can write a button script for "each" company's portal row's field and make that work, but this will be a ton of work as I add other forms of data that I'd like to save to the MainTable. Seems like there'd be a simple way to grab this info and copy it. I'm drawing a blank.

      Any ideas or suggestions how i can pull the value from a portal row and save it to a global field in my MainTable?

       

      Thanks for your advice guys!

        • 1. Re: Get Value of Field from a related table from a portal row
          philmodjunk

          10 different companies. All these tables are related to the main table by age.

          Does this mean that you have 10 different tables to 10 different physical tables? One for each company?

          and you want the same script to work from all 10 portals?

          That's possible, but your table/relationship design seems far from ideal.

          • 3. Re: Get Value of Field from a related table from a portal row
            ToddNolan

            So 10 Companies rate tables, and one mainTable. Related by "age".

            • 4. Re: Get Value of Field from a related table from a portal row
              philmodjunk

              So your answer to my question is "yes". Please note my comment that this is not an optimum design for what you want to do.

              Consider the design changes needed if you have to add an 11th company.

              But if you put all the data from all 10 tables into a single table, with an added table with one record per company, adding more companies becomes a data entry task and no design changes are needed. You can use relationships and/or portal filters to control which records from that combined table appear in a given portal so you can still pull up records for just one company when the need arises even though your data is all in the same table.

              But now to answer your question in terms of your current design:

              Option 1: Use the OnObjectenter trigger on a field or fields in your portal. You can then use:

              Get ( ActiveFieldContents ) to access the value of the field where you have set up this script trigger. Get ( ActiveFieldTableName ), on the other hand can return the Tutorial: What are Table Occurrences? name of that same field and this can be used with GetField To access data from a different field in the same portal record.

              Option 2: In Button Setup, you click "specify..." to find and select a script to run with the Perform Script button option. In the dialog that opens for selecting a script, you'll find an "optional script parameter" box. In that box, you can enter different field references to pass either the value of a field or fields from the portal record where the user clicked a button, or just the name of one or more fields. If you pass the name, use GetFieldName ( YourTableOccurrence::yourFieldName ) to pass that reference so that if you later rename this field, the correct field name is still passed to the script. If you pass the field name like this, GetField ( Get ( ScriptParameter ) ) will access the value of that field.

              • 5. Re: Get Value of Field from a related table from a portal row
                ToddNolan

                Thank you Phil. I always appreciate your advice. I have two issues with your options.

                First, using the current layout of this database, I've tried to use the Get(ActiveFieldContents). I right click on my portal field for each company's value that I want to use, and I created a script called: Trigger: RatePasser    - In the script code section: Get(ActiveFieldContents). Unfortunitely, whenever I click on that premium portal field, it doesn't do anything. If I change it from a Trigger to a button, then I can click on that field, but it still returns a blank. (To test it out I just used a show dialog box). Not sure what's checked or unchecked. Any ideas? 

                Secondly, if your suggesting I should just create two tables. One table (mainTAble) to house my criteria for selecting a rate (age, gender, tobacco, household discount, zip code, etc), and a final table to house all my rates for each company, I can do that. My issued is with creating a portal to show my rates accurately with this setup. Most companies use all those criteria above to find the correct premium for an individual, but there are a few companies that don't use one or even half of the criteria above to calcuate a company's premium.

                Example:

                if a user searches for a rate by:  age:65 gender:male tobacco: no zip 295 plan: plan F

                Company A: I can use the portal filter:  age = companiesTable::age and gender = companiesTable::gender and tobacco = companiesTable::tobacco and zip = companiesTable::zip and plan = companiesTable::plan

                Company D: Doesn't use a tobacco rate nor a zip code, how could I get the portal filter to avoid this? Because the fields would be blank due to the fact they don't matter.

                 

                Hopefully I didn't confuse you more. I can see it on my screen but it's sure to be confusing for others.

                I apologize. Do you need more inorder to answer this question?

                • 6. Re: Get Value of Field from a related table from a portal row
                  ToddNolan

                  I'm trying to allow a user to click on one of the company portal's rates and have that value save to Box1 and Box2 in the top right corner. Box 3 in the top right just adds box1 and box2. Hope this pic helps.

                  • 7. Re: Get Value of Field from a related table from a portal row
                    philmodjunk

                    Get ( ActiveFieldContents ) will work. But I didn't say to put it in a script parameter. And this option can't be used with a button  

                    And you seem to have missed the second suggestion as this is the one to use with a button. This second option would use a script parameter  

                    I don't see anything in your last post that changes my advice but that discussion needs a thread of its own.