12 Replies Latest reply on Aug 11, 2009 7:13 AM by mrvodka

    Script works inconsistently



      Script works inconsistently


      Hey folks,


      Im using FM Pro 9 (I have access to 9 Adv also).


      OK, here's the general idea with my database. There is a CPU table with computers and information about those computers (called CPU_Db). There is also an IP table with local and public IPs for those computers (called IP_Db). Both tables have an "Item Name" field which is the field that ties the two tables together via thier relationship. 


      I have a script that someone in a forum helped me create, it changes the related Item Name in the IP_Db table from within a form view of the CPU_Db table, and I do not allow editing of the Item Name manually without the script in either table. The reason I need the script is because changing the Item Name in either table without changing it to the same thing in the other will cause the related record in the other table to not be related anymore. The problem is that the script works inconsistently. Sometimes it doesnt change the related Item Name field in the IP_Db table.  Ive managed to figure out a few thing about when it works and when it doesnt, so there are some hints as to whats going on....I just dont know enough to nail it down and fix it. The best clue I have is if you run the script the first time without changing the name to a different name (just leave the default which is the current name) and then immediately run it it again but this time change the name, it will work everytime. This says to me that there is something that happens during the first run of the script that puts the database into a state that is conducive to running this script properly. I thought maybe it had something to do with open records so I added the Commit Records line at the top which wasnt there originally. I also added the same commit records line to the top of the scripts that run when you click buttons to switch layouts, but it doesnt help.


      Here is the script, any help would be greatly appreciated!!

      Commit Records/Requests []
      Set Variable [$OrigWindowName; Value:Get(WindowName)]
      If [Count ( IP_Db::Item Name ) = 0]
      .. Show Custom Dialog ["Enter New Item Here"; CPU_Db::Item Name]
      .. Exit Script []
      End If
      Go to Related Records [Show ONLY related records; From table: "IP_Db"; Using layout: "IP_Table" (IP_Db); New window]  // new window gets named "Related IPs" here

      Select Window [Name: $OrigWindowName]
      Show Custom Dialog ["Enter New Item Here"; CPU_Db::Item Name]
      If [Get(LastMessageChoice) = 2]
      .. Close Window [Name: "Related IPs"; Current file]
      .. Exit Script []
      End If
      Set Variable [$NewItemName; Value: CPU_Db::Item Name]
      Select Window [Name: "Related IPs"; Current file]
      Go to Record/Request/Page [First]
      .. Set Field [IP_Db::Item Name; $NewItemName]
      .. Go to Record/Request/Page [Next, Exit After Last]
      End Loop
      Close Window [Name: "Related IPs"; Current file]
      Select Window [Name: $OrigWindowName; Current file]

        • 1. Re: Script works inconsistently
             You really shouldnt be linking it this way. You should be using a key to join the tables together, not a meaning name field. Keep the item name with the item. Use a meaningless key to join them.
          • 2. Re: Script works inconsistently

            Hi Mr Vodka,


            Thanks for the response, but I need it to be linked by the name. I have a value list that is based on the names of all computers. Then, in the IP table, the first column is a list of IPs, and I use a drop down list of the computer names in the second column to assign computers to IPs, changing and moving them around when needed.


            Does anybody see anything in the script that needs work....or any troubleshooting suggestions?


            Thanks in advance for any help

            • 3. Re: Script works inconsistently
                 You still can have a value list based off the same and still use an ID. There is an option for display second field.
              • 4. Re: Script works inconsistently

                I guess I dont really understand what youre saying and how I would implement it. If I were to use an ID would I not need this script? How would I do that? How would the relationship work? If the value list I pick from in the IP table displays the names, and then I change the name in the CPU table, wouldnt that still break the relationship? Besides, even if the relationship stayed intact, I would want the name changed in both tables if the name of a computer needs to be changed. Maybe I dont understand....


                If this can be explained and would serve as a suitable substitute for the script, then I would appreciate any guidance. If not, I would love to get some help with the script.



                • 5. Re: Script works inconsistently

                  Basically, your IP table shouldnt be storing any computer names since the computer name would be stored in the CPU table. That is the purpose of a properly normalized db. Let's take an example that would showcase this issue a little more shall we.

                  Companies, Employees, Employment, Address.


                  If we stored the person's name in both the emplyoment, and address tables, look at all the effort that would be needed when an employee got married, divorced, etc to change their name...


                  Now if you had a meaningless ID key to represent the employee, then all that you would have to do is change it in the employee table and no where else because in the address and employment table, the employee is just referred to by the Employee ID field. Does this clear it up?

                  • 6. Re: Script works inconsistently

                    OK. I will try to set it up that way. I can see how it would be better to not depend on the names being the same, but Im not sure how it works. Can you explain a bit about how to set something like that up?



                    • 7. Re: Script works inconsistently

                      CPU table:

                      pkCPU_ID ( auto-serial increment )








                      IP table:

                      pkIP_ID ( auto-serial increment )

                      fkCPU_ID ( stores the primary key of the CPU ID ) 





                      • 8. Re: Script works inconsistently

                        The thing is that the IP table is used as if it were an independant database. List of IPs, with the next column being names of computers. I need it to work such that we can easily assign computers to IPs with a drop down menu of cpu names, and change or reassign computers easily to various IPs from within the IP table. What Im not getting from your posts is a clear idea of how to use IDs and still achieve what we need in terms of user experience.


                        I apologize for my lack of understanding, and would really appreciate if you could explain how to get it going in a systematic way so that I can understand.


                         Thanks again

                        • 9. Re: Script works inconsistently
                             I have sent you a PT.
                          • 10. Re: Script works inconsistently

                            Hey everyone,


                            I havent been able to get anywhere with this. Can anyone suggest any troubleshooting steps, or does anyone see anything in my approach or script that could be corrected?


                            Thanks in advance....

                            • 11. Re: Script works inconsistently

                              Just wanted to post the solution.


                              mr_vodka was very helpful and sent me a sample db which showed how it works. The thing I was missing that makes this whole thing work is the value list! In order to make the parent key and child key match, but still use the list of names effectively, you have to set up a value list to use TWO fields from the parent table (parent key and item name in my case). So when youre assigning a cpu to an ip (in my case), or entering whatever your data is into the child key field, the actual data entered is the first field (the parent key), but it displays the values from the second (in this case the computer name). The child key field also apparently has to be a pop up menu in order for this system to work. Im not sure why this is...but it doesnt seem to work right with a drop down list.


                              Thanks again mr_vodka!

                              • 12. Re: Script works inconsistently

                                You are welcome rowie.


                                A drop down list will display the second value at the time that it opens but once selected, it will display only the first field ( the key ). There are some work around tricks such as putting the related name field ontop of the drop down fieldso that it looks like it display it correctly, but those are work arounds. You can find out more info on them by looking around for examples.