4 Replies Latest reply on Nov 29, 2010 9:42 AM by philmodjunk

    How to get a 'current' record.

    JamesClark

      Title

      How to get a 'current' record.

      Post

      I'm building an equipment loans database I have the following tables Loans, inventory, kits and users I'm trying to create a script that will create a new loan record every time someone viewing an item in the inventory catalog clicks a 'check out' button.

      Clicking the script currently, is supposed to do the following: go to the Loan layout and set some of the fields. The fields are corresponding fields to the inventory and users tables so that it contains a productID (unique auto entered number from inventory table) and the FullName of the user. It also contains other fields from which data is extracted from counterparts in other tables, it also records the date. 

      Unfortunately it's not quite working out this way, for some reason every time a new loan record is created, a new inventory record is also created for some reason and this record contains no data about product names or anything except the product id and it is this that is sent to the corresponding product ID field in the new record in loans table. 

      Also I don't know how to set it so that it takes the name of the current user, I've told it to get the data from the fullname field of the users table, but I don't know which record it will take it from. I think I can solve this problem by creating a value list of users and prompting the user to choose who is checking out a piece of equipment but the problem is there is other data besides full name that I would like extracted. Users have unique ID's but I don't know how to use this unique ID so that the script knows to pull all data from the user record (or inventory record too for that matter) that matches this. 

      Ideally, one would find a piece of equipment from the catalogue, select one they want, press check out, be prompted for which user is checking out and have the correct information from both the record of the piece of equipment they were looking at in the catalogue (current inventory item) and the correct information from the user record matching that which they selected from the value list (current user).

      I have a link to the file here if anyone wants to take a look at it for me. I'm a little stumped. http://dl.dropbox.com/u/14796249/EQDB_rev5.2.fp7

        • 1. Re: How to get a 'current' record.
          philmodjunk

          The file you uploaded is locked with a password so I can't open it.

          You appear to have multiple issues and the starting point for dealing with all of them is to look at your relationship graph at Manage |Database | Reationships and compare that info to what you have selected in "Show records from" on the Layout setup... dialog for each of your layouts.

          "for some reason every time a new loan record is created, a new inventory record is also created"

          The most likely explanantion is that either your Loan and inventory layouts refer to the same data source table, or your script is creating a new record at a time when the wrong layout is current.

          "Also I don't know how to set it so that it takes the name of the current user, I've told it to get the data from the fullname field of the users table, but I don't know which record it will take it from. I think I can solve this problem by creating a value list of users and prompting the user to choose who is checking out a piece of equipment but the problem is there is other data besides full name that I would like extracted. Users have unique ID's but I don't know how to use this unique ID so that the script knows to pull all data from the user record (or inventory record too for that matter) that matches this. "

          Again, we'd need to know the structure of what tables you've defined and how they are related to answer this fully. It may be possible for a script that uses Get ( AccountName ) or Get ( UserName ) to automatically identify the current user for you--but that depends on how you've set up your system.

          It's also possible to set up a 2 column value list of user ID's and user full names. You use the name in column 2 to know who to select, but the value list enters the User ID number when you make the selection. Then, if you have the right relaitonship defined to your "loan" table, you can use that UserID to display the user's name when ever you look at a Loan record on your Loan layout.

          • 2. Re: How to get a 'current' record.
            JamesClark

            Well I've figured out the reason why it was creating records in the inventory table. When I created the loans layout I duplicated the inventory layout and edited it and forgot to change the associated table to loans and not inventory. My other questions are still my main problem though.

            • 3. Re: How to get a 'current' record.
              JamesClark

              Ah sorry I forgot about the password. 

              username: Admin

              password: passw0rd

              • 4. Re: How to get a 'current' record.
                philmodjunk

                First, you should link Users and Loans by UserID--not NameFull. Names are not unique and people change their names--both of which complicate the management of your data if you link by NameFull.

                You can use Manage | Value Lists to define a value list that lists all UserID values from Users in one column and NameFull in the Second column. Use the "Use Values from field option" to set this up. This enables you to use the user's full name to select and enter their user ID.

                You can display a user's name on your loan layout by either adding the NameFull field from Users to the Loans layout or you can format your Loans::UserID field as a popup menu and set the value list to only display values from the second field.

                More on the NameFull field.

                I'd define this field as text with an auto-entered calculation that combines first and last names and clear the "do not replace existing values..." option. (I'd also change it to list last names first if I have more than 10-15 users.) Then I'd set a unique values validation on this field so that it can catch instances where you might have two people with identical first and last names during data entry where I can then enter additional text in the first name field such as a middle initial to distinguish between users whose names would otherwise be identical. If you don't do this, you can't hide the first column of values nor can you sort by the NameFull field if you want to see the names listed in alphabetical order without also omitting the additional user with the same name.