1 2 Previous Next 16 Replies Latest reply on Jan 24, 2013 3:10 PM by linuxpad

    script with variables and lookups..

    linuxpad

      Title

      script with variables and lookups..

      Post

           OK Friends. I need your help again..

           I have multiple variables but this is just one of them..  I am looking to see if the temp is less than 80 and work load is a number.  Based on this info I would like to insert text from another table that is already build and has a relationship.

           So the results I am looking for would be:

           find JSA_Heat_Stress::Work_Load=1 and insert the text from JSA_Heat_Stress::Work_Break

           or

           when work_load=2 then find JSA_Heat_Stress::Work_Load=2 and insert the text from JSA_Heat_Stress::Work_Break

           and so on..

            

           Here is what I have so far..

           Case ( Temp  <=80 and Work_Load=1;  xxx;
                      Temp  <=80 and Work_Load=2;xxxx;
                      Temp  <=80 and Work_Load=3;xxxx;
                      Temp  <=80 and Work_Load=4;xx;
                      "Please Update Temp and Work Load" )

        • 1. Re: script with variables and lookups..
          philmodjunk

               This is a calculation and not a script.

               This doesn't add up for me.

               You seem to be saying you want to insert the same value for every case in your case function and this does not make sense:

               find JSA_Heat_Stress::Work_Load=1 and insert the text from JSA_Heat_Stress::Work_Break

               or

               when work_load=2 then find JSA_Heat_Stress::Work_Load=2 and insert the text from JSA_Heat_Stress::Work_Break

               Are you trying to search a table of records for a record where Work_Load is 1, 2, 3....???

               If so, that can't be done with the case function you have here.

          • 2. Re: script with variables and lookups..
            linuxpad

                 Phil..

                 Sorry for the confusion...  Let me see if I can make it a little more clear..

                 In the table JSA_Heat_Stress.. I have 4 records..  I would like to insert the text from the work_break field when the specific critera is met.

                 For instance..  when temp is less than 80 and work load is =1 then using the JSA_Heat_Stress::Work_Load=1 field to insert the text in the JSA_Heat_Stress::Work_Break field.  I have work_load 1-4 in this field and each one has different text.

                 I am then going to update the existing field with this data.

                 You are correct.. I will be using this for a lookup (in the database) not a script..  my bag...

                 Do I need to change it to a If function?

                 Does this make sense?

            • 3. Re: script with variables and lookups..
              philmodjunk

                   That makes more sense, but I'm not clear on what you are trying to do once you find the record with the specified temp and Work_Load. You seem to then be modifying a field in a record in the same table of 4 records. Which one? and why wouldn't this be a value entered into a field in a different, related table?

              • 4. Re: script with variables and lookups..
                linuxpad

                     ahh...

                     Yes.. I am using the calcualated value in this database field to do the lookup.. the table:field is JSA_Form:work break ratio

                     so.. when JSA_form:temp<=80 and JSA_form:work_load=1 then lookup in the JSA_Heat_Stress::Work_Load=1 field to insert the text in the JSA_Heat_Stress::Work_Break field into the JSA:form:work break ratio field.

                     or

                     when JSA_form:temp<=80 and JSA_form:work_load=2 then lookup in  where JSA_Heat_Stress::Work_Load=2 field to insert the text in the JSA_Heat_Stress::Work_Break field into the JSA:form:work break ratio field.

                     and so forth..

                      

                      

                • 5. Re: script with variables and lookups..
                  philmodjunk

                       If you defined this relationship between the two tables:

                       JSA_form::temp < JSA_Heat_Stress::temp AND
                       JSA_form::work_load = JSA_Heat_Stress::Work_Load

                       You can refer, from JSA_form, directly to JSA_Heat_Stress::Work break ratio to access the contents of that field from the record matching the specified criteria. This assumes that you enter 80 into the JSA_Heat_Stress::temp field in all 4 records.

                  • 6. Re: script with variables and lookups..
                    linuxpad

                         Phil..

                          

                         Yes you are correct.. but the temp is not in that table.. it's just used in the lookup formlua..  this is just one part of it..

                         for instance..

                         I will have multiple things going on..

                         Temp  <=80 and Work_Load=1 then lookup in the JSA_Heat_Stress::Work_Load=1 field to insert the text in the JSA_Heat_Stress::Work_Break field into the JSA:form:work break ratio field.

                         but if

                         Temp  <=90 and Work_Load=1 then lookup in the JSA_Heat_Stress::Work_Load=2 field to insert the text in the JSA_Heat_Stress::Work_Break field into the JSA:form:work break ratio field.

                         or

                         Temp  <=90 and Work_Load=2 then lookup in the JSA_Heat_Stress::Work_Load=3 field to insert the text in the JSA_Heat_Stress::Work_Break field into the JSA:form:work break ratio field.

                         I have a relationship between the tables to do the lookup and keep the records tied..  It has nothing to do with the calculated value/lookup..

                         make sense?

                          

                    • 7. Re: script with variables and lookups..
                      philmodjunk

                           but the temp is not in that table.. it's just used in the lookup formlua

                           Which is why I suggested that you add the temp to that table.

                           But this later example is quite different from the first.

                           You'll need to more fully explain the relationships and possible combinations for temperature and Work_Load. It's possible that a formula might be used or that you might have more than 4 records that specify both temperature and Work Load--which is the method I had in mind with my last post.

                      • 8. Re: script with variables and lookups..
                        linuxpad

                             ok..  sorry I was not clear.. The post I started with was only a portion of what I am looking to do.  I was just trying to keep it simple..

                        this formula will determine which JSA_Heat_Stress::Work_Break text goes in the JSA:form:work break ratio" field based on the "jsa_heat_stress:work_load" id..  so

                             it would look more like this:

                        Case ( Temp  <=80 and Work_Load=1; ---- lookup jsa_heat_stress:work_load" =1 and pass the text in JSA_Heat_Stress::Work_Break

                                   Temp  <=80 and Work_Load=2;----lookup jsa_heat_stress:work_load" =1 and pass the text in JSA_Heat_Stress::Work_Break
                                   Temp  <=80 and Work_Load=3;----lookup jsa_heat_stress:work_load" =2 and pass the text in JSA_Heat_Stress::Work_Break
                                   Temp  <=80 and Work_Load=4;----lookup jsa_heat_stress:work_load" =2(I will be setting this for each one, I have a chart that I have to lookup the temp and load to get the right heat stress text) and pass the text in JSA_Heat_Stress::Work_Break to JSA:form:work break ratio" field.

                                   Temp  <=90 and Work_Load=1;  xxx;
                                   Temp  <=90 and Work_Load=2;xxxx;
                                   Temp  <=90 and Work_Load=3;xxxx;
                                   Temp  <=90 and Work_Load=4;xx;

                                   Temp  <=95 and Work_Load=1;  xxx;
                                   Temp  <=95 and Work_Load=2;xxxx;
                                   Temp  <=95 and Work_Load=3;xxxx;
                                   Temp  <=95 and Work_Load=4;xx;

                                 

                                   Temp  >=96 and Work_Load=1;  xxx;
                                   Temp  >=96 and Work_Load=2;xxxx;
                                   Temp  >=96 and Work_Load=3;xxxx;
                                   Temp  >=96 and Work_Load=4;xx;


                                   "Please Update Temp and Work Load" )

                              

                        The relationship I have is using the id field.. just to link them..  jsa_form:JSA_ID ----jsa_heat_stress:jsa_sub_id

                        then I was going to run a if or case lookup against the jsa_heat_stress:work_load field.. (nothing to do with the jsa_form:work_load)  This may be where the confusion lies.. sorry for the bad naming schemes.. It's just a number id. for instance.. work_load=1,2,3,4.. etc.. then the text is in the JSA_Heat_Stress::Work_Break field (this is the text I want to populate into the "JSA:form:work break ratio" field..

                              

                              

                        • 9. Re: script with variables and lookups..
                          philmodjunk

                               And how do you do this with just 4 records in jsa_heat_stress?

                               how do the results for

                               Temp  <=80 and Work_Load=1

                               differ from the results for

                               Temp  <=90 and Work_Load=1

                               ??

                               As I posted earlier, this looks like a case where you need more than 4 records in jsa_heat_stress. It looks to me like you need one record in JSA_Heat_Stress for each combination of temp and work load. Then the relationship I posted previously correctly looks up the desired value from the Work Break Ratio field.

                          • 10. Re: script with variables and lookups..
                            linuxpad

                                 I will be specifying wich id  to pull in from the jsa_heat_stress:work_load field ----lookup jsa_heat_stress:work_load" =2(I will be setting this for each one, I have a chart that I have to lookup the temp and load to get the right heat stress text) and pass the text in JSA_Heat_Stress::Work_Break to JSA:form:work break ratio" field.

                                  

                            so the second part I will be coding the actual id to insert... I have to lookup each one on a chart (nothing to do with FM), then put the correct id in so it pulls the right text in the box.

                                  

                            maybe this will help.. attached is the chart i am using..

                            • 11. Re: script with variables and lookups..
                              linuxpad

                                   Just so I am not confusing anyone I will change the JSA_Heat_Stress::Work_Load to JSA_Heat_Stress::Work_ID

                                   again... this has no relation to the JSA_Form:work_load.  sorry if this was confusing..

                                    

                                    

                              • 12. Re: script with variables and lookups..
                                philmodjunk

                                     My brother the farmer has been commenting about this new issue so I am not unfamiliar with the concept.

                                     Using my last recommendation will work with this data. You'd have 25 records in your table, one for each "cell" of this chart. (Or you might not need records for the 6 white cells--cutting this down to 19 records.)

                                     There are ways you could reduce this to a formula to match to just one of 3 or 4 records, but for such a simple look up table, it's not worth the effort spent concocting that formula and should the government mandate a different set of standards in the future, you'd likely have to redesign such a formula where updating this table would likely be nothing more than a data entry excersize.

                                     If you don't want to enter the "break information" multiple times in each record, link in a table of three or 4 records to your Heat stress table  and then an ID field can be added to can link to the new table.

                                • 13. Re: script with variables and lookups..
                                  linuxpad

                                       Phil-

                                       I appreciate your input..   So..

                                       You said...

                                        

                                       "If you defined this relationship between the two tables:

                                       JSA_form::temp < JSA_Heat_Stress::temp AND
                                       JSA_form::work_load = JSA_Heat_Stress::Work_Load"

                                        

                                       Using this relationship then build out 25 records with the correct info?? 

                                        

                                       JP

                                  • 14. Re: script with variables and lookups..
                                    philmodjunk

                                         25 or maybe just 19. Your chart doesn't show any reslut returned for the white cells and if this is the case in your database, you don't need a record for each of those temp/workload combinations.

                                    1 2 Previous Next