3 Replies Latest reply on Jun 14, 2012 10:27 AM by philmodjunk

    Prohibit data entry based on status of existing records

    VeronicaDaigle

      Title

      Prohibit data entry based on status of existing records

      Post

      I have a table that keeps track of cards issued to employees and the dates they were issued/returned. The same card number may be returned and then reissued to another employee. Obviously the physical card can only be issued to one person at a time and I would like this to reflect in the database so that if the card issuer tries to issue a card that has an existing record with no return date (say they forgot to input that when the card was previously returned) then they are forced to put in the return date for the already issued card before they can enter a new record with that card number. I was thinking there must be some way to do this with data validation or a script or a combination of the two, if anyone has any suggestions on the most effective way to accomplish this I would love to hear them. Smile

        • 1. Re: Prohibit data entry based on status of existing records
          philmodjunk

          Can you describe the tables and relationships you have in place for "issuing cards"? That will help us to help you with a way to capture this user error.

          • 2. Re: Prohibit data entry based on status of existing records
            VeronicaDaigle

            There is one table called fuel cards. Each record has its own unique automatic serial ID since the card numbers themselves will be reused multiple times. There is a field for Card Number, PIN number, Card Issuer (which is related to a table with a list of supervising employees), Card Holder, Card Holder Company (which is related to a table with a list of of contracting companies), Date Issued, Date Returned, and a radio box for if the card was permanently deactivated on return. So each time a card is issued the issuer would fill in all of the information except for the return date. When the card is returned I have a script that allows the issuer to choose the card number from a dropdown on my home page and it searches for any records with that card number which do not have a return date to make it easy to find the card when the card holder returns it so they can enter the return date. What I don't want to happen is for the card issuer to be able to keep forgetting to put in the return date and have the same card shown as issued to multiple people at the same time.

            • 3. Re: Prohibit data entry based on status of existing records
              philmodjunk

              It appears that you have multiple records in FuelCards for the same card. I suggest you consider adding a table to your database where you have one record for each card and then your existing table becomes a record of each time that it is issued, but whether or not you do that is not an absolute requirement here. (But it would enable you to put a portal on a layout where you can see all the records for each time a given card was issued.)

              I would guess that your existing drop down is to a global field. The following script is based on that assumption:

              Go To layout [FuelCards (FuelCards)] //specify a layout where you can see and edit the date issued.
              Enter find mode [] //clear the pause check box
              Set field [FuelCards::CardNumber ; YourTable::GlobalCardNumberField ]
              Set Error Capture [on]
              Perform Find []
              If [ Get ( FoundCount ) // records were found]
                Enter Find Mode []
                Set field [FuelCards::ReturnDate ; "=" }
                contrain found set []
                If [Get (FoundCount) // records for this card exist with a missing return date]
                   Show Custom dialog ["This card cannot be issued to a new person until the ReturnDate on the record shown is updated to show the date that it was returned"]
                Else
                   Duplicate record
                End If
              Else
                 Show Custom Dialog ["This card was not found"]
              End If