7 Replies Latest reply on Feb 17, 2012 10:23 AM by glyoung

    Newbie trying to work with SQL Server

      I come from a database background using SQL Server. We have been directed to try to create applications using Filemaker Pro with SQL as the back end. I have a main table with a unique number that auto increments (via SQL) and therefore is of course considered not modifiable in the Filemaker Pro database. I have a layout where I’m showing all of the information about the location and this unique number is at the top. I want the users to be able to drop down this box (I have set it up as a value list) and when they choose a new number, it will trigger that the information will change based up on the number. The users will not have access to the find option in the toolbar, so I am trying to do this with a script trigger.

       

      When the user goes to the drop down box and choose the unique number they want, I expect all of the data below to change based upon this number. When I test this out I get the error "This action cannot be performed because this field is not modifiable." That makes sense if I was wanting to change that unique number for that specific record, but that's not what I'm trying to do.

       

      How can I make it so that when I choose this drop down box it will update the other data? I have tried making a Filemaker table and having it get the data from SQL for just the number but that doesn't seem to work either.

        • 1. Re: Newbie trying to work with SQL Server
          karendweaver

          Hi glyoung,

           

          (I would call you by name but you didnt sign your post)

           

          You could make the selection field a global field (one value for all records) and put a script trigger on the field so that when a selection is made, it performs a "find" to find the record for the selected number.

           

          warm regards,

          Karen

          • 2. Re: Newbie trying to work with SQL Server

            Thank you for your response Karen.  My name is Ginny.

             

            I went and looked up how to set up a global field, what I am finding is that I add global field in the Manage Database area and then choose options.  Unforunately options is grayed out for me.  I'm assuming because the table I'm trying to manage is actually a SQL table and not a filemaker pro table?  Should I create a new table that is specifically for Filemaker pro and create this there?  If so how does the data from SQL get into this new table?

             

            Ginny

            • 3. Re: Newbie trying to work with SQL Server
              karendweaver

              Hi Ginny

               

              Yes, I should've read your original request more carefully.

               

              I may be wrong - but I don't think you want to actually move the SQL data into the new table - you just want an easy way to display the data for the selected record.  The data remains in the SQL table.

               

              If that's the case, you could do what you want by creating a FileMaker table with the global field in it, then create a relationship to the SQL table that is based on the global field = the SQL number field.  Then base your layout on the FileMaker table and the fields on your layout to the SQL table relationship.  That way, only the selected record data will display. 

               

              You also should check out the White Paper on External SQL Sources:

               

              https://fmdev.filemaker.com/docs/DOC-1117

               

              Using FileMaker as a "front end" to a SQL database is not "recommended" by FileMaker - you are likely to run into some issues.  Get familiar with the white paper and with FileMaker as soon as you can - you will need to know how to build a database in FileMaker in order to do what you want and it is definitely not SQL. 

               

              Since you come from a SQL background - you may also find the free plugin SQLRunner from 2EmpowerFM to be really useful.  It allows you to use SQL commands in FileMaker scripts and calculations.

               

              Hope this helps!

               

              warm regards,

              Karen

              • 4. Re: Newbie trying to work with SQL Server

                Thank you for the info, I will definitely read the White Paper as soon as I can. 

                 

                It was not my choice to use Filemaker Pro as the front end (let's just say I've been kicking and screaming the entire way).  However I have been shown how quickly new applications can be developed in it  and how it could be used on mobile devices etc.  I have been directed to use it instead of the .Net technologies we have used in the past.  Since we already have data in SQL Server and have that infrastructure, it makes sense to me to have that as the back end. 

                 

                I'll look into the filemaker table and how I can get that set up.  I was hoping there was an easier way to just have it browse through records instead of updating when I change the drop down box.  But if that is the way I have to go I guess I'll have to go that way.

                 

                Ginny

                • 5. Re: Newbie trying to work with SQL Server
                  karendweaver

                  Hi Ginny

                   

                  You will love FileMaker - it definitely has lots to offer.  But - I am not sure what you are trying to do with "scrolling through records"  My impression was that you wanted to look at a specific record, one at a time (hence, select the record number to view).  but if what you want is to scroll through, why don't you do that?  There are lots of easier ways...

                   

                  Maybe some more info on what you are really trying to accomplish, then we could give you a better answer ;-)

                   

                  warm regards,

                   

                  Karen

                  • 6. Re: Newbie trying to work with SQL Server
                    beverly

                    Karen has given some great information.

                    Ginny has discovered that that primary key field in SQL is NOT modifiable (and it shouldn't be). However now Ginny may want to "Find" or "Go to" particular record(s)/row(s) based on the primary key values in a drop down.

                    Because the primary key is UNIQUE (that's it purpose!), finding it will only show one record/row. If the purpose is to "go to/jump to" a particular record/row based on the "selected primary" key, then we need to know.

                    If the search for record(s)/row(s) are to be made with additional field(s)/column(s), then a new Filemaker table can be created with the search fields as globals named the same. The user enters the criteria and clicks a button on the layout. A script called by this button would take all the criteria and enters find mode, set the SQL columns with the global fields and finally perform the find. No need to show the tool bar.

                    Or just give them the find and perform buttons on your header for the SQL table. You can assign the buttons (appropriatedly) with the single script step. They click the Find button and it enters find mode. They can search on any field displayed in the layout. They click on the Peform Find button, and it performs the find.

                    Beverly

                    • 7. Re: Newbie trying to work with SQL Server

                      I think this is what I was really looking for.  In my thinking browse mode meant "BROWSING" the data...instead it is also allowing updating of data.  By going into Find mode then when I use the drop down box it allows me to find the data instead of it trying to update the data.  I just added a button to do a perform find script.

                       

                      Thanks for pointing me in the right direction.