Prev
Next
Forums S3 Systems Administration

Screen Required Fields

Sort:
You are not authorized to post a reply.
Author
Messages
Layne
Basic Member
Posts: 24
Basic Member
2/27/2012 5:56 PM
    Does anyone know where in the database I can run a query that would tell me which fields on a given Lawson Screen are required and which fields are not.
    Myles Y
    New Member
    Posts: 1
    New Member
    5/8/2014 8:39 PM
      Hi Layne, I just did 2 hours of research because on this because my client wanted to know the same thing.
      Here's what i found:

      In the DEV9, PROD9 tables if you run this query you should see that there are no NULL able fields in the database. 

       
      

      
There are no fields in the database that allow nulls and there are no defaults in that database either. 
      
[CODE]
select object_Name(constid) as ConstratintName ,  object_name(id) As ConstraintParent, * from sysconstraints WITH (nolock)
where objectproperty(constid,'IsDefault')=1 or
objectproperty(constid,'IsDefaultCnst')=1 or
objectproperty(constid,'CnstIsColumn')=1 or
objectproperty(id,'TableHasDefaultCnst')=1

      In the GEN database there are two sets of tables that show a REQUIRED field.
      GEN.dbo.FILEREL (File Relationships) has a field named [REQUIRED] but that does not designate a required field on the table during insertion. It designates that a value being inserted is REQUIRED in the related table (Referential Integrity) unless there are Spaces being inserted in to the field by the UI.

      GEN.dbo.FILEIND (FileIndexes) has a field named ISPRIMARY. This indicates that this is the primary (CLUSTERED) index of the table. Clustered indexes may not ever contain null values so these values will always be inserted to the table. Join that FILEIND table to FILEINDFLD to get all of the fields that participate in the INDEX. You will see the required fields for the table.

      Having said all of this. I can not determine what is setting the default values because it is not obviously seen in the GEN database or the Environment Data Databases.

      At this point it is my assumption that the Application Layer handles the defaults and required fields.

      We are using AGS calls. I tested PA31 and found that submitting no SSN still created a row in the APPLICANT table.

      The reason I was looking for this infomration is that, I have created a Enterprise Data Dictionary. We use this to dynamically generate XML response SQL after mapping to the corresponding LAWSON fields. I exported all the field values (PRODUCTLINE, FILENAME, PREFIX, SYSTEMCODE, ORDINAL, TABLEFIELDNAME, ELEMENTNAME, LENGTH, DESCSIZE, SCRFLDNAME, DOCFLDNAME, DOCKEY, DOCTXT, VALUELIST) for all PROGRAMS, PRODUCTLINE.

      We are using this Data Dictionary to map between other third party systems.

      Lawson Fields are less than 25% of the data sources being mapped.

      I hope that Helps.

      Myles Yamada
      You are not authorized to post a reply.