7 Replies Latest reply on Sep 4, 2014 11:43 PM by schnook

    Script or relationship help?

    schnook

      Title

      Script or relationship help?

      Post

           Hello All

           They have coned me back again with a new management, lets see what happens this time.

           Now to the point, I need help with either a script or relationship setup or another way of doing something.

           We are using FMPA11 on win7 pc's, we have a vessel logon database what that is , is when a boat go out for the day they log on with us for the day and on their return they log off like the Coast Guard but we are all volunteers here. We have 2 databases a Membership database and a New Radio Log database which (see attached images) which are joined by the From field of the New Radio Log database  to the vessel registration field in the Membership database. Now what the problem is, is that when a member sells their boat (vessel) and the new boat rego is entered in their membership all the previous vessel records in the Radio Log database go blank. What I have though of doing is adding a 2 vessel details layout to enter the new vessel details in (Vessel Details 2 layout ), how can I have the Radio Log bring up the new vessel details from Vessel Details 2 layout, this is so as we don't loose pervious vessel records from their old boat.

            

           Any help or ideas will be greatly appreciated.

           Regards

           schnook

            

      Data.gif

        • 1. Re: Script or relationship help?
          philmodjunk

               You need to link records by a VesselID value that never ever changes no matter what happens to that vessel. The simplest FileMaker method is to define a field, I'd call it __pkVesselID as an auto-entered serial number and use it to link Vessel records to other tables. When a boat changes hands and data about that vessel changes such as the "Rego" (whatever that is), the serial number in this field remains unchanged and thus still links to the same related records.

               In my naming convention here, "pk" stands for "primary key" and I precede it with two underscores so that anytime FileMaker lists fields in alphabetical order, it automatically sorts to the top of the list, which often saves me time finding that field for a relationship, value list set up, etc...

               The way correctly defined primary keys complicate good user interface design and how to work around those complications to produce a user friendly interface is one of the topics covered in my forthcoming "Adventures In FileMaking #2" freeware database file so I've been reviewing and working on this very concept over the last few days.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Script or relationship help?
            schnook

                 Hi PhilModJunk

                 Thanks for the reply, would you mind guiding me on how to exactly do this step by step please. I don't really understand all this programming stuff I do try but I just can't seam to get a handle on it.

                 Regards

                 schnook

            • 3. Re: Script or relationship help?
              philmodjunk

                   The problem is that while I know what to do, I don't know your file, in particular, I don't know what tables and relationships will be affected by this change. These changes could be extensive and you will need to move forward cautiously with numerous saved back up copies to protect your data if a change made by you goes terribly wrong.

                   I'll provide an example here with just two fields. I'm using terms from your first post, but don't assume that they actually match your current design.

                   Let's say that you have two tables, Vessels and Radio Log linked by the field "VesselName" instead of a proper serial number based primary key:

                   Vessels::VesselName = RadioLog::VesselName

                   If someone risks bad luck by renaming their vessel, it will break the links for that Vessel to any existing records in RadioLog (sound familiar?) But to add in the serial number field, you need to be able to replace vessel name fields with the newly added serial number fields without also breaking the connection for your existing records.

                   Step 1, Go to Manage|Database|Relationships and add a new number field to Vessels named __pkVesselID. Define it to be an auto-entered serial number field.

                   Step 2; Save a back up copy of your file. Go to a Vessels layout and temporarily add the new __pkVesselID field to the layout. Switch to Browse mode, select Show All Records and then use Replace Field Content's Serial Number option to assign a serial number to every existing Vessels record in your table. Be sure to select the option that also updates your auto enter options. That way, if yo assign a value to 200 vessels in your table, creating a new record will produce a record with a value of 201 in this __pk field and you avoid getting a duplicate value.

                   Step 3; Return to Manage | Database | Fields and add a simple number field, _fkVesselID to the RadioLog table. This field is NOT an auto-entered serial number field.

                   Step 4; Save a back up copy of your file. Go to a Radio Log layout, add the _fkVesselID field temporarily to this layout, Show All Records and use Replace Field Contents to update this field. But instead of selecting the serial numbers option, use the calculation option and specify Vessels::__pkVesselID as the value to be entered into each field. (This layout must be based on an occurrence of Radio Log that is linked to Vessels in the relationship I posted at the beginning. )

                   Step 5; Save a copy of your file. Go to Manage | Database | Relationships and modify your existing relationship between Vessels and RadioLog to use __pkVesselId and _fkVesselID as match fields in place of VesselName.

                   Step 6: Review your database design in order to remove all instances of RadioLog::VesselName from your layouts, tables, scripts and calculations. Replace with Vessels::Vessel Name and modify any other design elements as needed to use the new relationship details in place of the old. Eventually, you'll be able to delete the VesslName field from the RadioLog table as this data should only be stored in the Vessels table.

              • 4. Re: Script or relationship help?
                schnook

                     Thanks PhilModJunk

                     I will play with this over the next few day I think I understand what you are saying, I do backup databases a lot during any modifications as I have destroyed quite a few in my time.

                     I will let you know how I go and thanks very much for your time and understanding.

                     Have a good weekend

                     Regards

                     schnook

                • 5. Re: Script or relationship help?
                  schnook

                  Hello PhilModJunk

                  Thank for your input, I have it working sort of, what I have found now is that the Membership Renewal Form still shows the old boats details, I have put a status field with a value list of A for active and N for not active, is there a script or script trigger that can have the new vessel details appear in the Membership Renewal Form. Have attached an image for you to ponder. The top picture is vessel detail section of the Membership Renewal Form, the 2nd pic vessel details (if boat is still the same) the information in this layout is what is showing even if we have entered a second vessel in vessel details 2 , the 3rd pic is Vessel details 2 which we will use to input the new vessel details if there were to sell there boat. what we need is something like if vessel details status = N (not active) then use vessel details 2 if status = A (active) to enter the relevant info into the first pic(membership renewal form). I hope this make sense, I think I just confused myself.

                   

                  Regards

                  schnook

                   

                  • 6. Re: Script or relationship help?
                    philmodjunk

                    When combining data from two different tables on the same layout, a key question to ask yourself is "Should I Lookup (Copy) this data from the related table or just display the actual data?"

                    My guess from what you report is that you are copying over data instead of just displaying it. I would guess that you have fields that use auto-enter field options (either looked up value or a calculaiton) to copy over data from the related table. Thus, when you change data in the related table directly, the data here, which is a copy of the original does not automatically update.

                    There are ways to force a relookup of this data, but the simpler approach is to remove the fields that copy data over and replace them on this layout with the fields from which they are copying data.

                    You might find the details fo this thread helpful: Auto Fill

                    • 7. Re: Script or relationship help?
                      schnook

                      Thanks heaps PhilModJunk, works like a charm.

                      Regards

                      schnook