14 Replies Latest reply on Jan 17, 2013 10:43 PM by JohhnyHilly

    Last created record changing by itself



      Last created record changing by itself


           Hi, I'm new to FileMaker so please bear with me.

           I am creating new records but for some reason the last record I enter changes by itself once I go to another layout. So for example, I have a "Company" field and a "Vessel Name" field. When I create the new record, I choose a company from a drop down list (value list) already created, then I type in the name of the vessel I want to add.

           When I leave this layout, go to another and come back, the company name has changed by itself to another company name from the same value list, while leaving the vessel name as I typed it. That is for the last record entered. The thing is, if I go and create a new record again, the one before that is now correct and the last entered record is again changing itself.

           I'm not sure if this is clear or if anyone needs an image of the relationships to find the problem.

           Any help would be much appreciated!!


        • 1. Re: Last created record changing by itself

               You'll need to tell us a lot more about your database.

               I'd guess that you have two tables, one for companies and one for vessels. How the two are related and how you have designed this layout will likely explain the change that you are seeing.

               Also when you change layouts, what are you then doing? Perhaps you are performing a find on it?

          • 2. Re: Last created record changing by itself

                 Hi Phil,

                 Yes correct, 2 tables. One for Contacts (company comes under this table as a field) and one for vessels. I have attached 2 images, one for the table and one for the relationship. I'm not sure if you can tell anything by these images.

                 As for the layout, I have attached a 3rd image. As you can see, there is a company field with a drop down value list form the contacts. I have set this as "COMPANY MATCHING FIELD" which is the foreign key that sits in the "Vessel" table. I did this as I found that if I set that field as "Company" from the Contacts table then I couldn't select any company from the drop down list. Then the field "Vessel Name" where you type in a name for the new vessel/record, a simple edit box.

                 It is here, between the Company and Vessel Name that changes very randomly. I can't tell you what I do specifically when I go to another layout as I can do the same things and nothing changes then suddenly will change for no apparent reason. I don't have any finds in my layouts yet so unlikely. Hope this is sufficient information. Let me know if you need more.


            • 3. Re: Last created record changing by itself

                   In layout setup...|Show Records From, a table occurrence is specified for each of your layouts. This is the name of one of the "boxes" shown in your screen shot of your relationships. Every layout in your database that refers to the same table occurrence in this drop down is referring to the very same current record, changes made to that record on one layout will be visible on the other layouts that refer to the same table occurrence.

                   The layout for which you have posted a screen shot is based on Vessels ("Vessels" is selected in "Show Records From"). If you select a company from the drop down, you are specifying to which company record the current vessel record is linked. (I can tell that, because "Company Matching Field" is not preceded with :: as you see for ::Client ID--a field from the related Contacts table.)

                   On what table is your other layout based? If it is also based on Vessels, then any change to data in the fields that you make is also being made to the very same record or possibly the same related contact record.

                   PS. matching records in a relationship by company name is not a good idea. Company names are not always unique. Companies change their names. And there's always the possibility that a company name might be entered incorrectly. All of these possibilities will create issues for your data when you link the records by name like this. A serial number from a table of Companies avoids all of these potential problems.

              • 4. Re: Last created record changing by itself

                     Ok Phil, I have found the problem from your advice above. I have another Layout that is based on the "Vessel" table and have the field "Vessel Name" in it to select a vessel to display information in a portal. When I select the vessel from this drop down menu it changes the vessel name in the previous layout but same record.

                     Soooooo this brings me to a new question. On this second layout I have a portal that is displaying information (that comes from another table - Oil Samples) about the vessel that is selected in the "Vessel Name" field. How do I have a drop down menu to select a vessel to display this portal information without it changing the record?

                     I've attached a screenshot of how it is currently set up. Just to clarify, Oil Sample info is inputted into a portal and is assigned to a Vessel Name through the "Vessel Name Matching Field" in the Oil Samples table. This second layout I mentioned above has a portal based on the Oil Sample table which displays information when I select the vessel name which brings me back to the current problem. If you need any more info please let me know and I'll post it. Thanks for finding the previous problem, so simple now that I look at it! Much appreciated :)


                • 5. Re: Last created record changing by itself

                       First of all, linking records by Vessel name is not a good idea. It would seem quite possible that you could get two vessels with the same name. And while it is considered bad luck to do so, a vessel's name could be changed. And it's also pretty easy to mis-enter a vessel's name. All of those scenarios can create problems for any related records linked by a vessel's name. Use an internally generated serial number to identify the vessel record.

                       What you need to do, if I am correctly picturing the situation, is use a drop down of vessel names and IDs to find a record for the selected vessel.

                       You can define a global field to use for this purpose. You select a vessel in the global drop down field and then a script--probably performed by a script trigger uses the data in the global field to perform a find.

                  • 6. Re: Last created record changing by itself

                         Yes you are correct in what you say about the situation. Sorry Phil, is there any chance you could tell me how I can do this exactly? Although I understand what you say to do, I'm not entirely sure how I go about it. Thanks.

                    • 7. Re: Last created record changing by itself

                           By "this" do you mean using the global field to find records? There are two ways to do that: 1) use a scripted find performed via a script trigger or 2) add another occurrence of your table and link it to the global field such that you put a portal to this new table occurrence on your layout--this does not require scripting but does require making changes in Manage | Database | Relationships.

                           In which direction do you want to take this?

                      • 8. Re: Last created record changing by itself

                             Yes it does. I'm thinking probably the scripted find, however, I would be interested to know how to do the same via the relationship change. That is if you have time of course :)

                        • 9. Re: Last created record changing by itself

                               In other words: "Both" wink

                               A scripted find: (I am assuming that this layout refers to "vessels" in Layout Setup...|Show Records From.)

                               Enter Find Mode []--->clear the pause check box
                               Set field [Vessel::Vessel Name ; GlobalFields::gVesselName]
                               Perform Find[]

                               A portal linked to the global field.

                               Put gVesselName--the global field with your drop down in the Vessel table. (Or any table you choose on which to base this layout.)

                               Link it like this:

                               Vessel::gVesselName = OilSamplesBygVesselname::Vessel Name Match Field

                               OilSamplesBygVesselName woud be a new occurrence of OilSamples that you create by selecting it in Manage | Database | Relationships and clicking the duplicate button (two green plus signs).

                               Then you put a portal to OilSamplesBygVesselName on your layout and any vessel you select in the global field's drop down will produce the matching list of oil Samples records for that vessel.

                               Please note that I have built these examples off of your existing vessel name based relationships. I've already recommended that you change to using ID numbers instead of vessel names. Both of the above methods can readily be adapted to use ID numbers instead of names and your drop down value list can list both ID numbers and Vessel Names.

                          • 10. Re: Last created record changing by itself

                                 Done! Ok so the portal linked to the global field worked. Thank you!  I couldn't get the scripted find to work though. For this do you need to create a table called "GlobalFields" then put the field gVesselName in there? Under Set Field in the script I inputted the Vessel::Vessel Name ; GlobalFields::gVesselName as you listed above but a window appears saying " An operator (e.g. +, -, *...) is expected here."


                                 And yes I am going to change to using Vessel ID's. To adapt the above method do I just change the global field to gVesselID instead of gVesselName?

                            • 11. Re: Last created record changing by itself

                                   Global fields not used in a relationship, can be defined in any table. I often set up a special table such as one named GlobalFields for that purpose.

                                   When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                              • 12. Re: Last created record changing by itself

                                     Phil, in the instance of using the table occurance OilSamplesbygVesselName method (not the scripted find), how would I use the "Go to Related Record"? I want to select a vessel on the vessel layout and have a button that takes me to the layout with the portal on that is described in this thread. I have tried it but it doesn't seem to select the the approprite vessel as it is a global field. Anyway around this?

                                • 13. Re: Last created record changing by itself

                                       How do you "select a vessel" on the vessel layout? Is this a global field with a value list?

                                       Are both layouts based on Vessel in Layout Setup?

                                  • 14. Re: Last created record changing by itself

                                         Ok problem solved. Yes it is selected by a global field and yes both are based on vessels. I had two different global fields for selecting the vessel on each of the two layouts, so changed it to the same and linked the portal to the new global variable. Thanks.