1 2 Previous Next 22 Replies Latest reply on Feb 23, 2012 7:37 AM by comment

    Show one thing in form but write different to the database.

      Newbie, coming from .Net programming and SQL background.

       

      I have 5 fields that store a 1 or 0 in them. I have tried making these part of a radio button group and for the one item you choose that field would get written as a 1 and all of the others as a 0. But it seems that radio buttons are just stored in one field in Filemaker Pro.

       

      Another option I have tried is to have them just all be drop down boxes. Is there a way to show a Yes in the box, but have it actually write 1 to the database instead of Yes or no?

       

      Any help is appreciated.

       

      Ginny

        • 1. Re: Show one thing in form but write different to the database.
          beverly

          Hey, Ginny! for question one:

          you could have another field to hold the radio button/value list. o1 o2 o3 o4 o5

          then have your 5 fields be auto-enter based on this new field. 1 = if(radio=1; 1; 0), 2 = if(radio=2; 1; 0), etc.

           

          for question two. you can format a number field as BOOLEAN and specify Yes=1/true, No=0/false (or specify other words to show in your boolean.

           

          See if that helps,

          Beverly

           

          On 22 Feb 2012, at 8:54 AM, glyoung asked in whole or in part:

           

          Newbie, coming from .Net programming and SQL background.

           

          I have 5 fields that store a 1 or 0 in them.  I have tried making these part of a radio button group and for the one item you choose that field would get written as a 1 and all of the others as a 0.  But it seems that radio buttons are just stored in one field in Filemaker Pro.

           

          Another option I have tried is to have them just all be drop down boxes.  Is there a way to show a Yes in the box, but have it actually write 1 to the database instead of Yes or no?

           

          Any help is appreciated.

           

          Ginny

           

          • 2. Re: Show one thing in form but write different to the database.
            Mike_Mitchell

            Hello, Ginny. Welcome to FileMaker!

             

            Radio buttons are an interface layer convention. They have no inherent "smarts" of their own. However, you can, through the use of Script Triggers (which are the FileMaker equivalent of events) make them behave as though they have smarts.

             

            In cases such as you suggest, you can write a script that performs the functions you want. For example, if you want only one of your 5 fields to have a "1" in it, then you would write a script to invert all the others. You can use a script parameter to identify which field was clicked (so you only have to write a single script).

             

            To your other question - can I have the radio button show "Yes", but write a "1" instead - that's a little trickier, but can be done. What you could do in that case is set your value list up against a table with two fields. In one field you have your "1", and the second field contains "Yes". You have your value list set up to show only the "Yes" (the second field), but, in actuality, it puts the "1" in the field.

             

            Let me know if you need more detail.

             

            Mike

            • 3. Re: Show one thing in form but write different to the database.

              So following up on both of your suggestions.  If I added a field for the radio button, would I then add the script trigger to that radio button.  And that script that was triggered would then have the If logic to write to the correct fields?

              • 4. Re: Show one thing in form but write different to the database.
                Mike_Mitchell

                Ginny -

                 

                Correct. You can add an OnModify trigger to the field so that your script would execute when the radio button was clicked. And your script would have the branching If - Else If logic to set the other fields to the appropriate values.

                 

                Mike

                • 5. Re: Show one thing in form but write different to the database.
                  Mike_Mitchell

                  Or, you can do as Beverly suggests and embed it in the data layer using auto-enter calculations. Either way will work; it will depend on exactly how you want it to work. Her way is arguably better if you want it always to work that way (i.e., if you don't want it to be tied to the interface).

                   

                  Mike

                  • 6. Re: Show one thing in form but write different to the database.

                    I guess I'm confused when you say do it Beverly's way.  It seems to me you are both talking about the same thing.  I am going to add a radio button field, and when the button is chosen then a script runs that will use the if logic. 

                     

                    On another note is there a way to just go and write a script without using this interface to click on different options? 

                    • 7. Re: Show one thing in form but write different to the database.
                      MicheleOlson

                      Mike wrote...

                      "To your other question - can I have the radio button show "Yes", but write a "1" instead - that's a little trickier, but can be done. What you could do in that case is set your value list up against a table with two fields. In one field you have your "1", and the second field contains "Yes". You have your value list set up to show only the "Yes" (the second field), but, in actuality, it puts the "1" in the field."

                       

                      Or... how about using conditional formatting with a "Yes" text object placed on top of the Radio button "1" display. If the radio button field = 1, change the color to the background. For the "Yes" object, change the "Yes" to black or whatever.

                       

                      [Note: I am very 'into' conditional formatting as well as merge variables of late... so those approaches pop into my mind first... thanks very much to LaRetta and Karen W. for pointing me down that road... so much less weight in the database ... no fields to define... etc.]

                       

                      Michele

                      • 8. Re: Show one thing in form but write different to the database.
                        Mike_Mitchell

                        Ginny -

                         

                        In your Manage Database dialog, double-click on any field of type Text, Number, Date, Time, Timestamp. You'll see this dialog:

                         

                        dialog.png

                         

                        Click the checkbox that says, "Calculated value". You'll get the calculation dialog:

                         

                        dialog.png

                         

                        In this dialog, you can define a calculation that FileMaker will use to insert data into the field automatically. In your case, you'll want to have the calculation look something like this:

                         

                        Case ( InterfaceField = 1 ; 1 ; 0 )

                         

                        where InterfaceField is a single field with the five radio buttons in it (1 through 5) and this calculation goes on your first field. You'd do something similar for each of your other 5 fields - except you'd say InterfaceField = 2 for field #2, InterfaceField = 3 for field #3, and so forth.

                         

                        This would automatically enter your data at the data layer instead of the interface layer. It's six of one, half dozen the other, but Beverly's solution is a little cleaner (it gives you only one field for users to enter instead of 5) and it will work across multiple layouts (so you don't have to duplicate your Script Triggers). Hence, it might be an overall better solution.

                         

                        As to your other question, I'm not sure exactly what you're asking. Yes, you can write a script to do just about anything you want ... but are you asking if you can write a script to perform this specific function without the user having to click on something? What is the end goal you're trying to reach? Maybe we can help you get there.

                         

                        Mike

                        • 9. Re: Show one thing in form but write different to the database.
                          beverly

                          Ginny, I said to create another field for the radio buttons. The in the definition of your 5 other fields use an auto-enter calculation. NO SCRIPTS (to trigger).

                           

                          Beverly

                          • 10. Re: Show one thing in form but write different to the database.
                            Mike_Mitchell

                            Michele -

                             

                            Hmn. Hadn't considered that, but why couldn't you just put a text object "Yes" and "No" over the top of the "1" and "0"? What does Conditional Formatting do for you - since selecting the radio button moves the selection anyway?

                             

                            I must be missing something ...

                             

                            Mike

                            • 11. Re: Show one thing in form but write different to the database.

                              Sorry, I'm just very overwhelmed with all of this information. 

                               

                              Beverly - I think you're telling me that I should set up the 5 other fields to be auto enter type fields correct?  Well I can't do that because they are SQL tables and when you go into manage database you are unable to change anything about the fields. 

                               

                              Mike - thank you for showing me where that info is, again like I said, I am unable to make those fields be auto calculations, because of the back end being SQL Server.

                               

                              I have been considering taking all of this off of SQL Server and just putting it strictly in Filemaker, but this program has been around for several years and already has quite a bit of data already in the SQL Database.  I had hoped to not have to recreate each of the tables in Filemaker, but I'm beginning to think that maybe I will have to.  Unfortunately that will add a lot of time to this project, as there does not seem to be any way to script my old tables and recreate them.  (All have to be manually created.)

                               

                              My question about writing scripts, I came across some scripts in some of my documentation that showed me some If Then Else Logic and I wanted to just cut and paste that and then edit it.  When I go into the Edit Script area and try to just copy it there, it doesn't allow me to do that.  It seems to require that I click on the If control and then click Specify to choose my fields etc.  I was just trying to find an easier way to cut, paste and update what seemed me to be fairly simple logic.

                               

                              I think I need to try to get back out my training and go back to the beginning and see if I can't get a better understanding of some of these things before I ask any more questions.

                               

                              Thanks again for your patience and help.

                               

                              Ginny

                              • 12. Re: Show one thing in form but write different to the database.

                                I need to retract those statements.  I CAN update the fields and make them calculated fields.  It just did not allow me to add any new fields other than calculated or summary.  I stand corrected. 

                                 

                                Thank you for your help.   What I did was create a new field and have the other fields set as auto-calculation.

                                 

                                Ginny

                                • 13. Re: Show one thing in form but write different to the database.
                                  MicheleOlson

                                  Conditional formatting shows or hides the text and the 1 or 0.

                                  Hadn't considered that, but why couldn't you just put a text object "Yes" and "No" over the top of the "1" and "0"?

                                   

                                  What you have described is what I was trying to describe. You likely did a better job.

                                   

                                  Using the conditional text "Yes" or  "No" to me means one or more less fields to define and thus a slightly faster display.

                                   

                                  At least that is what my experience has been.

                                   

                                  Michele

                                  • 14. Re: Show one thing in form but write different to the database.
                                    comment

                                    MicheleOlson wrote:

                                     

                                    Conditional formatting shows or hides the text and the 1 or 0.

                                    Hadn't considered that, but why couldn't you just put a text object "Yes" and "No" over the top of the "1" and "0"?

                                     

                                    What you have described is what I was trying to describe.

                                     

                                    Why woudn't you use just the text object (set to be a button) and remove the field from the layout altogether?

                                     

                                    Alternatively, you could use the field itself (formatted as Edit box, Boolean) as the button.

                                    1 2 Previous Next