7 Replies Latest reply on Oct 26, 2011 6:33 PM by schnook

    Outstanding Vessel Script Help

    schnook

      Title

      Outstanding Vessel Script Help

      Post

      HELLO ALL

      WE HAVE A VESSEL LOG ON DATABASE THAT THE GENERAL PUBLIG CALL US AND LOG ON FOR THE DAY,

      THE FIELDS THAT WE USE ARE

      FROM=VESSEL REGISTRATION,

      FISRT LOG IS A VALUE LIST THAT CONTAINS YES & NO,

      CLEARED IS ALSO A VALUE LIST CONTAINING YES & NO.

      NOW SOMETIMES A VESSEL CAN HAVE 4-6 ENTRIES FOR THE DAY

      EG: WHEN THE VESSEL FIRST MAKES CONTACT FIRST LOG = YES, CLEARED = NO,

      THEN ANY OTHER ENTRIES THAT THEY ARE NOT SIGNING OFF CONTAIN FIRST LOG = NO, SIGNED OFF = NO,

      NOW WHEN THEY DO SIGN OFF FOR THE DAY FIRST LOG = NO, SIGNED OFF = YES

      NOW WHAT I WOULD LIKE TO DO IS TO CREATE A BUTTON WITH A SCRIPT THAT WILL ONLY SHOW VESSELS THAT HAVE NOT SIGNED OFF, BUT ONLY THEIR LATEST ENTRY THAT SIGNED OFF = NO, IN A TABLE VIEW FORMAT.

      CAN SOMEONE PLEASE IN DUMMY TERMS HELP ME WITH THIS.

      WE ARE RUNNING WIN 7 WITH FMP ADVANCED 11

      REGARDS

      SCHNOOK

        • 1. Re: Outstanding Vessel Script Help
          philmodjunk

          Is there a date or timestamp auto-entered with each record?

          I'm thinking it may work better to have two tables for this. A First Log in creates a record in table1 and each subsequent check in creates a related record in Table2. The sign off at end of day then updates the record in table1.

          This can be done with a portal to log the subsequent check in calls and now you can do a very simple search for all records in Table1 where the vessel has not signed off for the day.

          • 2. Re: Outstanding Vessel Script Help
            schnook

             Hi Phil

            Thanks for your time

            We have only one table for the what we call the Radio Log linked to our membership database via vessel registration, and yes it has a creation date with each entry and a modification time if we were to change any info on a created log.

            We have heaps of other tables like O/N (overnight vessel log), ESL (extended stay log), overdue log, etc.

            I don't have the knowledge to change things that much that is why i thought a loop and omitt record script might be the best way for me to go.

            What i am trying to have happen is say we were to click on the outstanding button it will only show us the vessels that are still on our log that have not signed off for the day. At the moment we look at a table called Todays Log and that shows us every entry that has been created today, now we have a sort from button that sorts the record by vessel registration we can see via this table which vessels are till outstanding but we could have up to 200 entries in this table and it takes some time to work ouy which are still out.

            Hopes this helps

            Regards

            schnook  

            • 3. Re: Outstanding Vessel Script Help
              philmodjunk

              It would be very easy to add that extra table and doing so would eliminate the need for such a script.

              Using the existing table structure, you'd need to find all records with today's date that have "yes" in the First Log or "yes" in the signed off fields (create two requests), then sort them by Vessel ID to group the fields by Vessel with sorting by first log in descending order included so that the first Log field is sorted ahead of the signed off field.

              Now you can use a loop to mark or make a list of Records where there is no matching signed off record.

              Hmmm, just thought of an alternate approach....

              Define this self join relationship: (create an extra occurrence of your table and name it "VesselLogOnbySIgnOff".

              VesselLogON::Date = VesselLogOnbySignOff::Date AND
              vesselLogOn::VesselID = VesselLogOnbySignOff::Vessel ID AND
              VesselLogOn::FirstLog = VesselLogOnBySignOff::SignedOff

              Use this script to find all records that logged on today and have not signed off:

              Enter Find Mode []
              Set Field [VesselLogOn::Date ; get ( CurrentDate )]
              Set Field [VesselLogOn::FirstLog ; "Yes"]
              New Record/Request
              Set Field [VessleLogOnbySignOff::VesselID ; "*"]
              Omit Record
              Set Error Capture[on]
              Perform Find[]

              This finds all log on records with todays date and FirstLog = "yes", but omits those that have a related record--which will be those that have a matching Sign Off record.

              • 4. Re: Outstanding Vessel Script Help
                schnook

                 G'day Phil

                Trying to understand this

                Are you saying make a copy of the radio log database and change the copes name to VesselLogOnbySIgnOff?

                Define this self join relationship: does this mean in layout setup show records from the radio log.

                Then create a button and run the above script with it? 

                Regards

                schnook

                • 5. Re: Outstanding Vessel Script Help
                  philmodjunk

                  We should not create a duplicate table here, just add another occurrence of the same table.

                  Open manage | database | Relationships.

                  Click the box for this table to select it. This is called a table occurrence.

                  Now click the duplicate button to make a new occurrence of this table. (Button with two plus signs.)

                  This does not duplicate your table, it creates a duplicate "label" here needed in order to create this relatiionship.

                  Double click this new box to bring up a dialog box where you can change its name like I have.

                  Now drag from one occurrence to the other to create the relationship I've shown. The easiest way to link multiple pairs of fields in one relationship like this is to drag to create the relationship line, then double click this line to bring up a dialog where it's a bit easier to add additional pairs of fields like this.

                  Since "table occurrence" is a new term here, you might want to investigate this tutorial on the subject:  Tutorial: What are Table Occurrences?

                  • 6. Re: Outstanding Vessel Script Help
                    schnook

                    Hey Phil 

                    VesselLogON::Date = VesselLogOnbySignOff::Date AND VesselLogOn::VesselID = VesselLogOnbySignOff::Vessel ID

                    AND VesselLogOn::FirstLog = VesselLogOnBySignOff::SignedOff

                    If my table is called New Radio Log would the above now look like the one below

                    New Radio Log::Date = VesselLogOnbySignOff::Date

                    AND New Radio Log::From = VesselLogOnbySignOff::Vessel ID  (From being vessel registration (vessel ID)

                    AND New Radio Log::First Log For The Day = VesselLogOnBySignOff::SignedOff

                    THE FIELDS THAT WE USE IN OUR NEW RADIO LOG ARE

                    FROM = VESSEL REGISTRATION,

                    FISRT LOG FOR THE DAY  IS A VALUE LIST THAT CONTAINS YES & NO,

                    CLEARED IS ALSO A VALUE LIST CONTAINING YES & NO

                    Now i create a extra occurrence table of our New Radio Log Table and change the name to VesselLogOnbySIgnOff and create the above relationships between the two tables is this correct.

                    Regards

                    schnook

                    • 7. Re: Outstanding Vessel Script Help
                      schnook

                      G'day Phil

                      Thanks for all your time once again it works a treat, just takes a little time doing the find but we can live with that and thanks for the info links it helped me understand a little will now try with other ideas we have. Once againg thanks very much for your time.

                      Regards

                      schnook