Skip to content

Lists and Values Text File Schema Definition Tutorial

This tutorial will show you how to build a Text File Schema Definition that, in addition to the two tutorials previously completed, will also contain the information used to define and validate the Tutorial Text File using Lists and Values. For this tutorial, we are going to add a List with six Values. We are then going to set the "State" Field to only allow members of the new List.

Tutorial Text File

last_name,first_name,state,zip_code,price,date,notes
Craig,Kai,OK,73755,15499,12/26/2019,"friendly, prompt and good tipper"
Wilcox,Jack,,,12458,7/14/2017,very nice
O'Neil,Kasper,WY,82937,15499,3/7/2011,paid by certified check
Marks,Sopoline,CT,06778,4859,8/21/2014,"never paid, do not do any further business"
Kelly,Colorado,,,9899,6/3/2018,

Built Suggested Text File Schema Definition

Type=CONTROL,ID=1,SchemaEnclosureCharacter=22,Version=1.0.1.22,,,,,,,,,,,,,,,,,
Type=META,ID=2,Name=Smythee Estuary Importers - Sales Summary Text File Schema Definition,Annotation=This file had been prepared by Doug Jones to match the records given 12/30/2019,Version=1.0.0.1,FileType=DELIMITED,DelimiterCharacter=2C,EnclosureCharacter=22,EncloseWhen=NEEDED,CharacterEncoding=UTF-8,EndOfLine=NEWLINE,FieldIDMethod=HEADER,SchemaURL=https://www.smytheeestuaryimporters.com/schemas.html,OwnerName=James Smythee,OwnerEmail=james.smythee@smytheeestuaryimporters.com,OwnerPhone=123-555-1234,,,,,
Type=RECORD,ID=3,Name=Header Record,Annotation=This defines the header record,Position=1,ParentID=2,Required=YES,HasData=ALWAYS,MinimumAllowed=1,MaximumAllowed=1,KeyField=,ParentKeyField=,RecordIDPosition=,RecordIDValue=,RecordRow=1,Width=,IsHeader=YES,TrailingDelimiter=NO,,,
Type=RECORD,ID=4,Name=Data Record,Annotation=This defines the data records,Position=2,ParentID=2,Required=YES,HasData=ALWAYS,MinimumAllowed=1,MaximumAllowed=,KeyField=,ParentKeyField=,RecordIDPosition=,RecordIDValue=,RecordRow=,Width=,IsHeader=NO,TrailingDelimiter=NO,,,
Type=FIELD,ID=5,Name=Last Name Header,Annotation=,Position=1,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=9,MaximumLength=9,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^last_name$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=last_name
Type=FIELD,ID=6,Name=First Name Header,Annotation=,Position=2,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=10,MaximumLength=10,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^first_name$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=first_name
Type=FIELD,ID=7,Name=State Header,Annotation=,Position=3,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^state$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=state
Type=FIELD,ID=8,Name=ZIP Code Header,Annotation=,Position=4,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=8,MaximumLength=8,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^zip_code$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=zip_code
Type=FIELD,ID=9,Name=Price Header,Annotation=,Position=5,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^price$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=price
Type=FIELD,ID=10,Name=Date Header,Annotation=,Position=6,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=4,MaximumLength=4,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^date$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=date
Type=FIELD,ID=11,Name=Notes Header,Annotation=,Position=7,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^notes$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=notes
Type=FIELD,ID=12,Name=Last Name,Annotation=,Position=1,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=1,MaximumLength=50,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=O'Neil
Type=FIELD,ID=13,Name=First Name,Annotation=,Position=2,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=1,MaximumLength=50,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=Kasper
Type=FIELD,ID=14,Name=State,Annotation=,Position=3,ParentID=4,Required=YES,HasData=OPTIONAL,MinimumLength=2,MaximumLength=2,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=WY
Type=FIELD,ID=15,Name=ZIP Code,Annotation=,Position=4,ParentID=4,Required=YES,HasData=OPTIONAL,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^[0-9]{5}$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=82937
Type=FIELD,ID=16,Name=Price,Annotation=,Position=5,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=,MaximumLength=,DataType=NUMBER,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=1,NumericMaximumValue=99999999,Start=,Width=,Alignment=,FillCharacter=,Sample=15499
Type=FIELD,ID=17,Name=Date,Annotation=,Position=6,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=,MaximumLength=,DataType=DATE TIME,DateTimeFormat=M/d/yyyy,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=3/7/2011
Type=FIELD,ID=18,Name=Notes,Annotation=,Position=7,ParentID=4,Required=YES,HasData=OPTIONAL,MinimumLength=1,MaximumLength=255,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=paid by certified check
Type=SUMMARY,ID=19,ControlCount=1,MetaCount=1,RecordCount=2,FieldCount=14,ListCount=0,ValueCount=0,ConditionCount=0,RuleCount=0,SummaryCount=1,TotalCount=19,,,,,,,,,

Steps To Build The Lists and Values Text File Schema Definition

  1. Open the Suggested Text File Schema Definition file in a text editor
    1. https://textfileschema.omegatower.net/schemas/tutorial_suggested.tfsd
    2. Save it with a new file name
    3. Don't forget to save it frequently and often as you build the Text File Schema Definition
  2. Open the Text File Schema Definition template file in a text editor
    1. https://textfileschema.omegatower.net/schemas/tfsd_template.tfsd
    2. Copy the List Record and paste it in the new Lists and Values Text File Schema Definition file just before the Summary Record
    3. Copy the Value Record and paste it in the new Lists and Values Text File Schema Definition file just before the Summary Record, repeating 5 times
  3. Update the List Record, using its definition as a guide, to add more information to assist other users
    1. Set ID to "19"
      It's the 19th record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Allowed States"
      Naming the list will facilitate any discussions or other documentation.
    3. Set Position to "3"
      It's the third record belonging to its parent record: the Meta Record.
  4. Update the first Value Record, using its definition as a guide, to define the first Allowed State
    1. Set ID to "20"
      It's the 20th record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Connecticut"
      Naming the value will facilitate any discussions or other documentation.
    3. Set Position to "1"
      It's the first value belonging to its parent list: the "Allowed States" List Record.
    4. Set ParentID to "19"
      The ID of its parent record, the "Allowed States" List Record, goes here.
    5. Set Value to "CT"
      Enter the actual value of the list member here.
  5. Update the second Value Record, using its definition as a guide, to define the second Allowed State
    1. Set ID to "21"
      It's the 21st record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Maine"
      Naming the value will facilitate any discussions or other documentation.
    3. Set Position to "2"
      It's the second value belonging to its parent list: the "Allowed States" List Record.
    4. Set ParentID to "19"
      The ID of its parent record, the "Allowed States" List Record, goes here.
    5. Set Value to "ME"
      Enter the actual value of the list member here.
  6. Update the third Value Record, using its definition as a guide, to define the third Allowed State
    1. Set ID to "22"
      It's the 22nd record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Montana"
      Naming the value will facilitate any discussions or other documentation.
    3. Set Position to "3"
      It's the third value belonging to its parent list: the "Allowed States" List Record.
    4. Set ParentID to "19"
      The ID of its parent record, the "Allowed States" List Record, goes here.
    5. Set Value to "MT"
      Enter the actual value of the list member here.
  7. Update the fourth Value Record, using its definition as a guide, to define the fourth Allowed State
    1. Set ID to "23"
      It's the 23rd record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Oklahoma"
      Naming the value will facilitate any discussions or other documentation.
    3. Set Position to "4"
      It's the fourth value belonging to its parent list: the "Allowed States" List Record.
    4. Set ParentID to "19"
      The ID of its parent record, the "Allowed States" List Record, goes here.
    5. Set Value to "OK"
      Enter the actual value of the list member here.
  8. Update the fifth Value Record, using its definition as a guide, to define the fifth Allowed State
    1. Set ID to "24"
      It's the 24th record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Vermont"
      Naming the value will facilitate any discussions or other documentation.
    3. Set Position to "5"
      It's the fifth value belonging to its parent list: the "Allowed States" List Record.
    4. Set ParentID to "19"
      The ID of its parent record, the "Allowed States" List Record, goes here.
    5. Set Value to "VT"
      Enter the actual value of the list member here.
  9. Update the sixth Value Record, using its definition as a guide, to define the sixth Allowed State
    1. Set ID to "25"
      It's the 25th record in the Schema Definition, so this is a logical choice for the ID.
    2. Set Name to "Wyoming"
      Naming the value will facilitate any discussions or other documentation.
    3. Set Position to "6"
      It's the sixth value belonging to its parent list: the "Allowed States" List Record.
    4. Set ParentID to "19"
      The ID of its parent record, the "Allowed States" List Record, goes here.
    5. Set Value to "WY"
      Enter the actual value of the list member here.
  10. Update the Summary Record, using its definition as a guide, to correct its information
    1. Set ID to "26"
      It's now the 26th record in the Schema Definition, so change this to match.
    2. Set ListCount to "1"
      There is now one List Record in the Schema Definition, so change this to match.
    3. Set ValueCount to "6"
      There are now six List Records in the Schema Definition, so change this to match.
    4. Set TotalCount to "26"
      There are now 26 records in the Schema Definition, so change this to match.
  11. Update the tenth Field Record "State", currently ID=14, using its definition as a guide, to add the new List
    1. Set ListID to "19"
      Since the State field can only have one of these new values, setting its ListID to the ID of the new List accomplishes that.

Built Lists and Values Text File Schema Definition

Type=CONTROL,ID=1,SchemaEnclosureCharacter=22,Version=1.0.1.22,,,,,,,,,,,,,,,,,
Type=META,ID=2,Name=Smythee Estuary Importers - Sales Summary Text File Schema Definition,Annotation=This file had been prepared by Doug Jones to match the records given 12/30/2019,Version=1.0.0.1,FileType=DELIMITED,DelimiterCharacter=2C,EnclosureCharacter=22,EncloseWhen=NEEDED,CharacterEncoding=UTF-8,EndOfLine=NEWLINE,FieldIDMethod=HEADER,SchemaURL=https://www.smytheeestuaryimporters.com/schemas.html,OwnerName=James Smythee,OwnerEmail=james.smythee@smytheeestuaryimporters.com,OwnerPhone=123-555-1234,,,,,
Type=RECORD,ID=3,Name=Header Record,Annotation=This defines the header record,Position=1,ParentID=2,Required=YES,HasData=ALWAYS,MinimumAllowed=1,MaximumAllowed=1,KeyField=,ParentKeyField=,RecordIDPosition=,RecordIDValue=,RecordRow=1,Width=,IsHeader=YES,TrailingDelimiter=NO,,,
Type=RECORD,ID=4,Name=Data Record,Annotation=This defines the data records,Position=2,ParentID=2,Required=YES,HasData=ALWAYS,MinimumAllowed=1,MaximumAllowed=,KeyField=,ParentKeyField=,RecordIDPosition=,RecordIDValue=,RecordRow=,Width=,IsHeader=NO,TrailingDelimiter=NO,,,
Type=FIELD,ID=5,Name=Last Name Header,Annotation=,Position=1,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=9,MaximumLength=9,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^last_name$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=last_name
Type=FIELD,ID=6,Name=First Name Header,Annotation=,Position=2,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=10,MaximumLength=10,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^first_name$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=first_name
Type=FIELD,ID=7,Name=State Header,Annotation=,Position=3,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^state$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=state
Type=FIELD,ID=8,Name=ZIP Code Header,Annotation=,Position=4,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=8,MaximumLength=8,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^zip_code$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=zip_code
Type=FIELD,ID=9,Name=Price Header,Annotation=,Position=5,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^price$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=price
Type=FIELD,ID=10,Name=Date Header,Annotation=,Position=6,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=4,MaximumLength=4,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^date$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=date
Type=FIELD,ID=11,Name=Notes Header,Annotation=,Position=7,ParentID=3,Required=YES,HasData=ALWAYS,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^notes$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=notes
Type=FIELD,ID=12,Name=Last Name,Annotation=,Position=1,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=1,MaximumLength=50,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=O'Neil
Type=FIELD,ID=13,Name=First Name,Annotation=,Position=2,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=1,MaximumLength=50,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=Kasper
Type=FIELD,ID=14,Name=State,Annotation=,Position=3,ParentID=4,Required=YES,HasData=OPTIONAL,MinimumLength=2,MaximumLength=2,DataType=STRING,DateTimeFormat=,ListID=19,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=WY
Type=FIELD,ID=15,Name=ZIP Code,Annotation=,Position=4,ParentID=4,Required=YES,HasData=OPTIONAL,MinimumLength=5,MaximumLength=5,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=^[0-9]{5}$,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=82937
Type=FIELD,ID=16,Name=Price,Annotation=,Position=5,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=,MaximumLength=,DataType=NUMBER,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=1,NumericMaximumValue=99999999,Start=,Width=,Alignment=,FillCharacter=,Sample=15499
Type=FIELD,ID=17,Name=Date,Annotation=,Position=6,ParentID=4,Required=YES,HasData=ALWAYS,MinimumLength=,MaximumLength=,DataType=DATE TIME,DateTimeFormat=M/d/yyyy,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=3/7/2011
Type=FIELD,ID=18,Name=Notes,Annotation=,Position=7,ParentID=4,Required=YES,HasData=OPTIONAL,MinimumLength=1,MaximumLength=255,DataType=STRING,DateTimeFormat=,ListID=,StringRegex=,NumericMinimumValue=,NumericMaximumValue=,Start=,Width=,Alignment=,FillCharacter=,Sample=paid by certified check
Type=LIST,ID=19,Name=Allowed States,Annotation=,Position=3,,,,,,,,,,,,,,,,
Type=VALUE,ID=20,Name=Connecticut,Annotation=,Position=1,ParentID=19,Value=CT,,,,,,,,,,,,,,
Type=VALUE,ID=21,Name=Georgia,Annotation=,Position=2,ParentID=19,Value=GA,,,,,,,,,,,,,,
Type=VALUE,ID=22,Name=Montana,Annotation=,Position=3,ParentID=19,Value=MT,,,,,,,,,,,,,,
Type=VALUE,ID=23,Name=Oklahoma,Annotation=,Position=4,ParentID=19,Value=OK,,,,,,,,,,,,,,
Type=VALUE,ID=24,Name=Vermont,Annotation=,Position=5,ParentID=19,Value=VT,,,,,,,,,,,,,,
Type=VALUE,ID=25,Name=Wyoming,Annotation=,Position=6,ParentID=19,Value=WY,,,,,,,,,,,,,,
Type=SUMMARY,ID=26,ControlCount=1,MetaCount=1,RecordCount=2,FieldCount=14,ListCount=1,ValueCount=6,ConditionCount=0,RuleCount=0,SummaryCount=1,TotalCount=26,,,,,,,,,
Back to top