3 Replies Latest reply on Oct 23, 2012 1:58 PM by philmodjunk

    Va;ue list with container fields?



      Va;ue list with container fields?


           Starting a new project.  It will be a database for personnel records.  I want to create a "Checklist" of items that an employee working in HR will need to gather from the new hire.  I also want the checklist to contain a container field for each item, and when a PDF is uploaded into the container field, a check is put in the checklist box.

           Is it possible to load a checklist with container fields depending on what is selected in, say, the "Hire Type" field?

           Also, I would want to create a portal for "other" fiels that need to be scanned and uploaded into the record.  I assume it is not possible to combine the 2 into 1 big happy portal?



        • 1. Re: Va;ue list with container fields?

               One big portal sounds exactly right. Scripts can create the related records as needed in response to the options selected by the user.

               Instead of an actual field for your check boxes, you might put layout text such as a tick mark or a capital X on your layout and use conditional formatting to make the character visible or invisible depending on the value returned in the conditional format expression.

               IsEmpty ( yourPortalTable::ContainerField )

               Could change the font size to 100+ to make the X or tick mark disappear.

          • 2. Re: Va;ue list with container fields?

                 This makes sense.  I would attach a script trigger to a value list.  So if I choose the "Contractor" option from the value list, 8 related records would be added to my portal.  Each row would have a field called "File Type"  and would auto populate (Resumé, application, etc).  Then I'd be able to upload into each container field.  I think this is a bit different than your method.  I just can't seem to find the correct script steps, and FM doesn't allow you to type freely......

            • 3. Re: Va;ue list with container fields?

                   It sounds exactly like what I had in mind. You see I have a database for a California Certified Recycler that has to process a very large number of customers very quickly and accurately. Since over 90% of the used beverage containers being redeemed represent one of 4 specific line items on our purchase orders, I do exactly what you describe to prepopulate the line items portal with these 4 most commonly redeemed types of UBCs.

                   Here's a generalized script that recreates how we do it, adapted to the idea that different sets of records need to be created based on the value selected from the value list.

                   First the tables involved. I will work from the following tables, related in the following manner:


                   DocTypeList::Category = Employees::Category
                   Employees::_pkEmployeeID = Documents::_fkDocumentsID

                   For an explanation of the above notation: Common Forum Relationship and Field Notations Explained

                   DocTypeList has at least two fields: Category, FileType

                   The script, performed via OnObjectSave (drop down list) or OnObjectModify ( Pop up menu or radio buttons):

                   Freeze Window
                   Set Variable [$Count ; Count ( DocTypeList::Category ) ]
                     Set Variable [$K ; value: $K + 1]
                     Exit Loop If [$K > $Count ]
                     Set Variable [$FType ; value: GetnthRecord ( DocTypeList::FileType ; $K ) ]
                     Set Variable [$EmpID ; value: Employees::__pkEmployeeID ]
                     Go To Layout [Documents ( Documents ) ]
                     New Record/Request
                     Set Field [Documents::_fkEmployeeID ; $EmpID ]
                     Set Field [Documents::FileType ; $FType ]
                     Go To Layout [original layout ]
                   End Loop

                   A few notes about the DocTypeList table. The purpose of this table is that it enables you to modify the list of records loaded into your portal without having to redesign any parts of your database as it's a simple data entry task. You can set up the tables, one of two ways and they will both work with this script without any modification:

                   1) Make it one record for every combination of Category and FileType. If the same FileType is used in two categories, add one record for each.

                   2) Use a check box group format for the Category field. If the same FileType is used in two Categories, select the appropriate check box values for both categories in the same record with that FileType.