1 2 Previous Next 15 Replies Latest reply on Jun 11, 2009 10:49 AM by philmodjunk

    Automated insertion of Number based on Textual value of a dropdown menu

    nader

      Title

      Automated insertion of Number based on Textual value of a dropdown menu

      Post

      Hello everyone,

      I am new to FMP and any help would be appreciated.

      I have to related tables, What Im trying to do is to insert a number in a filed in second table based on a selection from first table. Values in the first table is a text entry and they are E , L, N and LD in a dropdown menu. I want FMP to insert 7.5, 7.5, 11 and 11 into different field on the second table.

       

      Is this possible and How???

       

      Thank you all in advance for helpipng me

        • 1. Re: Automated insertion of Number based on Textual value of a dropdown menu
          TSGal

          Talimar:

           

          Thank you for your post.

           

          Yes, you can insert information into a related field based upon a value from a field in another table, and this can be accomplished via a script and a script trigger.

           

          Visually think of what you want to accomplish automatically after the value is entered in the first table (for this example, TABLE1).  That is, take that value, switch to the record in the related table (TABLE2), and insert into the related field, the number based on that original value.  Let's put this into a script.

           

          Pull down the Scripts menu and select "Manage Scripts..."

           

          In the bottom left corner of the Manage Scripts dialog box, click the "+ New" icon to create a new script.

           

          Name the new script "UpdateValue", and select and add the following script steps:

           

          Go to Related Record (From table: "TABLE2" ; Using layout: <layout name> (TABLE2) ]

          Set Field [ <related field> ; Case ( TABLE1:: <field> = "E" or TABLE1:: <field> = "L" ; 7.5 ; 11) ]

          Go to Layout [ <layout name> (TABLE1) ]

           

          -------

           

          That is, go to the related record in TABLE2, and set the field to either 7.5 (if original field from TABLE1 is "E" or "L" ) or 11 ("N" or "LD" ).

           

          Close the script and save it. 

           

          Now, we need to "trigger" this when the value is entered into the original field.  Therefore, go to the layout that displays TABLE1 information, pull down the View menu and select "Layout Mode".  Click once on the drop-down field (so handles appear on the four corners of the field), pull down the Format menu and select "Set Script Triggers..."

           

          In the "Set Script Triggers" dialog box, check the option "OnObjectSave" and select the script "UpdateValue".  Click OK.

           

          Go to Browse Mode and change the value of the field to "E".  Once you do, the value in the related table is updated to 7.5.

           

          This should get you pointed in the right direction.

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Automated insertion of Number based on Textual value of a dropdown menu
            philmodjunk
              

            The best way is to set up a table with the letter and number values. You can then create a relationship between this table and your main table and then use either script, layout, field lookup or a calculation to do what you want.

             

            First the basics:

            Create a new table. I'll call it "LookUps".

            Define two fields of the types shown in parenthesis:

            Letter (text)

            Number (number)

             

            You should have matching fields in your main table (I'll call it "MainTable") of the same types. If you don't, create them.

            Letter (text)

            Number (number)

             

            In your relationships graph create a relationship linking the two tables:

            MainTable::Letter----= ----LookUps::Letter

             

            Now go back to your field definition for MainTable::Number, double click it and select the looked up value option from the Auto-Enter tab.

            Select "LookUps" from the "Look up from related table" menu and click "Number" to select the number field in the LookUps table.

            Click Ok three times to get out of Manage | Database.

            Go to the LookUps layout, create 4 records and enter the 4 pairs of letters and numbers into these records.

            Now you are done.

            Any time you enter/modify the letter in MainTable::Letter, MainTable::Number will receive a copy of the matching number field in the LookUps table.

             

            This does exactly what you asked. However, you may or may not actually want to copy the info from the LookUps table. It depends on exactly what you are trying to do.

            In this example, if you later decide that entering an "E" in the letter field should return 8.5 instead of 7.5, changing the value in the LookUps table, will cause 8.5 to be entered into the Number field of any new MainTable records, but existing records will remain unchanged. That may be what you need, it may not. If you want the number values in MainTable to automatically update, let me know and I'll show you some other options.

            • 3. Re: Automated insertion of Number based on Textual value of a dropdown menu
              nader
                

              Thanks phil for detailed solution.

              It worked like a charm but there is only a small problem. I need to have the Sum of each filed separetly which is at the moment not yet possible. I need to do something like this:

              Sum(Field 1) where Entry in Table A <Field 1> is "E"

              Sum<Field 1> where enrty in table A <Field 1> is "L"

              and so on

               

              What I am trying to do is to design a timesheet summery based on staff roster placement. E is for Early shift, L is for Late shift, LD is long day and N for Night shift, there will be other options for staff to choose like DO (Day off) and so on

               

              I have managed to design the roster and likn it with the timesheet summery but adding them up seperately to give a sum of each shifts totals is now presenting a problem.

               

              Thank you again for your help.

              • 4. Re: Automated insertion of Number based on Textual value of a dropdown menu
                nader
                  

                TSGAL,

                Thank you very much for your quick reply to my post. Becuase of my limited knowledge of FMP, it was a bit difficult for me to write the script and redirect all my related fields to that script. any way thank you so much for replying to my post.

                • 5. Re: Automated insertion of Number based on Textual value of a dropdown menu
                  TSGal

                  Talimar:

                   

                  Since I provide assistance on such a wide range, I'm always trying to determine the knowledge level of the customer.  It is a balancing act.  If I am too basic, the customer may feel like I'm not talking up to their level and a bit miffed, and if I am too advanced, the customer may feel overwhelmed, confused, and not want to deal with me again.  The bottom line is that I want to help, and since PhilModJunk provided a great answer that works for you, that is all that should matter.  :-)

                   

                  Let's move to your second problem...  adding/summing values for Early, Late, Long Day and Night.

                   

                  To add values over a group of records involve a Summary field.  Pull down the File menu and select "Manage -> Database...".  Click on the Fields tab (if not already showing), and enter a new field "Total".  On the right side, next to "Type", click the pop-up and select "Summary", and click Create.  A new dialog box appears.  Select the option "Total of" (which is probably the default), highlight your "Number" field, and click OK.  Click OK again and return to Browse.

                   

                  At the bottom of the current layout, you should now see a "Total" field with an amount.  This is the total of Number for the entire found set of records.  If all records are showing, then it will be for all records.  If only one record is showing, then the Number field and the Total field will be the same value.

                   

                  If you perform a Find for Letter = "E", the Total represents the total amount for all "E" records.

                   

                  Caveat:  If you perform a Find for Letter = "L", you will get the "L" records AND the "LD" records.  To find just the "L" records, go to Find and enter "L" in the Letter field (like before), and then pull down the Requests menu and select "Add New Request".  You are presented with a second Find screen (if you look at the Find Requests in the Status Bar, you will now see 2 of 2 requests).  Enter "LD" in the Letter field, and then click "Omit" in the Status Bar.  Press return and you will only see "L" records.  In short, you are searching for all records that begin with "L", but you are omitting any that contain "LD".

                   

                  Pull down the View menu and select "Layout Mode".  Then, pull down the Layouts menu and select "New Layout/Report..."

                   

                  For "Select a layout type", highlight "List/Report" and click "Next".

                   

                  For simplicity sake, select "Columnar list/report" (probably the default) and click "Next".

                   

                  For Specify Fields, select "Letter" and "Total" fields only.

                   

                  For Sort Order, select the "Letter" field.

                   

                  For Select a Theme, choose Default or Standard.

                   

                  For Header and Footer information, just click "Next".

                   

                  Create a script, and call it "TOTALS".  (You can always rename it later under the Scripts menu)

                   

                  View the report in Layout mode and click "Finish". 

                   

                  -------

                   

                  On the left edge, you will see Header and probably the letter "B" (for Body).  Double-click the "B", and a "Part Definition" dialog box appears with the selection set for "Body".  Change the option to "Sub-Summary when sorted by" and select the "Letter" field.  When you click "OK", you are prompted if you want this to appear above or below the Body.  In this case, it doesn't matter as we are removing the Body.

                   

                  Now, pull down the View menu and select "Browse" mode, and you should see totals for each of the Letter values.

                   

                  Return to your original layout, perform a find for a group of records, and then you can pull down the Scripts menu and select "TOTALS", and it will switch to this new layout and display the totals for that found set.

                   

                  This should probably get you pointed in the right direction.  Let me know if you need clarification for any of the above steps.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Automated insertion of Number based on Textual value of a dropdown menu
                    nader
                      

                    Thanks TSGAL,

                    absoluetly spot on. Thank you so much

                    • 7. Re: Automated insertion of Number based on Textual value of a dropdown menu
                      nader
                        

                      Hello everyone,

                      I have a problem hope sombody could direct me to a correct path.

                      I am running a nursing home and want to calculate my staff working hours. staff receive different rates if work day shift or night or saturday shifts and so on.

                      I have a table consists of Date, Shift type( E for Early shift, L for Late shift ....), Hours worked(Number), Night Hours(Number) Saturday Hours(Number) and Sunday Hours(Number)

                      I am trying to run this script as On record commit triger:

                       

                      if[TIMESHEET ALT::Shift Type = "E"] Setfield[TIMESHEET ALT::Hours Worked = "7.5" Enf If if[TIMESHEET ALT::Shift Type = "L"] Setfield[TIMESHEET ALT::Hours Worked = "7.5" Enf If if[TIMESHEET ALT::Shift Type = "LD"] Setfield[TIMESHEET ALT::Hours Worked = "11.5" Enf If if[TIMESHEET ALT::Shift Type = "N"] Setfield[TIMESHEET ALT::Hours Worked = "10.5" Setfield[TIMESHEET ALT::Night Hours = "10.5" Enf If If[TIMESHEET ALT::Date = "Friday", and TIMESHEET ALT::Shift Type = "N"] Setfield[TIMESHEET ALT::Sat Hours = "7.5" End If If[TIMESHEET ALT::Date = "Saturday", and TIMESHEET ALT::Shift Type = "E"] Setfield[TIMESHEET ALT::Sat Hours = "7.5" End If If[TIMESHEET ALT::Date = "Saturday", and TIMESHEET ALT::Shift Type = "L"] Setfield[TIMESHEET ALT::Sat Hours = "7.5" End If If[TIMESHEET ALT::Date = "Saturday", and TIMESHEET ALT::Shift Type = "LD"] Setfield[TIMESHEET ALT::Sat Hours = "11.5" End If if[TIMESHEET ALT::Date = "Saturday", and TIMESHEET ALT::Shift Type = "N"] Setfield[TIMESHEET ALT::Saturday Hours = "3" Setfield[TIMESHEET ALT::Sunday Hours Hours = "7.5" End If If[TIMESHEET ALT::Date = "Sun", and TIMESHEET ALT::Shift Type = "E"] Setfield[TIMESHEET ALT::Sun Hours = "7.5" End If If[TIMESHEET ALT::Date = "Sun", and TIMESHEET ALT::Shift Type = "L"] Setfield[TIMESHEET ALT::Sun Hours = "7.5" End If If[TIMESHEET ALT::Date = "Sun", and TIMESHEET ALT::Shift Type = "LD"] Setfield[TIMESHEET ALT::Sun Hours = "11.5" End If if[TIMESHEET ALT::Date = "Sun", and TIMESHEET ALT::Shift Type = "N"] Setfield[TIMESHEET ALT::Sunday Hours = "3" End If If[TIMESHEET ALT::Date = "Bank Holiday", and TIMESHEET ALT::Shift Type = "E"] Setfield[TIMESHEET ALT::Bankholiday Hours = "7.5" End If If[TIMESHEET ALT::Date = "Bank Holiday", and TIMESHEET ALT::Shift Type = "L"] Setfield[TIMESHEET ALT::Bankholiday Hours = "7.5" End If If[TIMESHEET ALT::Date = "Bank Holiday", and TIMESHEET ALT::Shift Type = "LD"] Setfield[TIMESHEET ALT::Bankholiday Hours = "11.5" End If if[TIMESHEET ALT::Date = "Bank Holiday", and TIMESHEET ALT::Shift Type = "N"] Setfield[TIMESHEET ALT::Bank Holiday Hours = "3" End If

                      Unfortunately this script does not work and inserts for example sat hours regardless of dates or type of the shift etc.

                      Can anyone help to come up with another more practical script?

                      Thnk you so much for your help.

                      • 8. Re: Automated insertion of Number based on Textual value of a dropdown menu
                        philmodjunk
                          

                        I'm guessing that Alt:: Date is a field of type date, Thus ALT:: Date = "Friday" will never be true because you are comparing a number field (that's all a date field really is) to the literal text "Friday". Rewrite each of your expressions like this:

                         

                        DayName(ALT:: Date) = "Friday"

                         

                        You also have unneeded quotes around your numbers, but I don't think that will affect how the expressions evaluate.

                        • 9. Re: Automated insertion of Number based on Textual value of a dropdown menu
                          nader
                             Thank you absolutly spot on....
                          • 10. Re: Automated insertion of Number based on Textual value of a dropdown menu
                            nader
                              

                            I have a problem here and have been trying everything within my little knowladge of FMP10 to make it work but so far have not much luck. Any help would be grately appreciated.

                            I have a list of employees each with a performance average (Number), I am trying to setup an automated roster in which at least two employee with an average of 10 or above is automatically selected for each shift. there will be 3 different shifts available E for Morning, L for afternoon, and N for Night.

                             

                            I have been trying to approach the problem with sorting and filtering method so far I am not getting anywhere, PLEASE PLEASE if there is another solution to this problem can you help me.

                             

                            Thank you all for reading this

                            • 11. Re: Automated insertion of Number based on Textual value of a dropdown menu
                              philmodjunk
                                

                              Can you describe the issue in more detail?

                               

                              I can't tell from your post if you are trying to get a value list, a script or some other part of filemaker to work for you.

                              • 12. Re: Automated insertion of Number based on Textual value of a dropdown menu
                                nader
                                  

                                Thank you for replying to my post.

                                I have a table (Staff) each employee has a performance score of 5 to 25, My second table (Shifts) has Two fields(shift date) and (shift type), shift type is a dropdown menue, values are M for morning, A for afternoon and N for Night shift. I am trying to setup a procedure so that the FMP can suggest a rota where there are at lease 2 employee with a perfomance score of 10 or more and another 2 employee with performance score of less than 10 are selected for each day and each shift.

                                 

                                I am running a nursing home and i have to have 2 qualified and 2 unqualified staff on duty for every shift. I have made a table of employees and based on their performances, they have a score. I am trying to use the performance indicator as a base to set up an automated rostering software. I already have a manual rota where shift type have to be selected manually and from that rota, i am able to even calculate my staff salary. but it is a very time consuming task and I was wondering if there is a way to do this automatically.

                                 

                                Thank you again for replying to my post.

                                • 13. Re: Automated insertion of Number based on Textual value of a dropdown menu
                                  philmodjunk
                                    

                                  You should be able to sort your table of employees by performance score and then use a script to step through them assigning each to a different shift in turn. I can't tell from what you describe as to why this wouldn't work for you.

                                   

                                  Here's a general outline of your script.

                                   

                                  Perform a find to find all available high performance employees (Score > 10)

                                  Step through the records in the found set, assigning each employee to a shift until all shift assignments have been made

                                   

                                  Perform a find to find all available low performance empolyees (Score < 10)

                                  Step through the records in the found set, assigning each...

                                   

                                  If there is significant disparity between employee scores in each group, you may want to assign one employee to each shift and then repeat a second time to assign the second employee--that way one shift doesn't get the two highest scoring employees and another the two lowest.

                                   

                                  Will this approach work for you?

                                  • 14. Re: Automated insertion of Number based on Textual value of a dropdown menu
                                    nader
                                      

                                    Thank you for your reply.


                                     

                                    Perform a find to find all available high performance employees (Score > 10)

                                    Step through the records in the found set, assigning each employee to a shift until all shift assignments have been made

                                     

                                    Perform a find to find all available low performance empolyees (Score < 10)

                                    Step through the records in the found set, assigning each...

                                     

                                     


                                     

                                    The first step is the one I have no problem with, but the second step is the problem, can you suggest a some kind of procedure or script to make the second step so that once an employee is assigned to a shift, he or she will be taken off the found set for further shifts on that day and one more question:

                                    If a database is made in FMP10 advance could it work with other versions of FM like FM8 for example.

                                     

                                    tahnk you so much for your help

                                    1 2 Previous Next