Populate and Extract update

[Bob Sneidar]

Hi all. 

I have an update to the POPULATE code I posted. This has a new argument for exclusions, so that you can skip table columns in case you need to. I found that tables with shared column names (like custid for example) were getting overwritten on a form that displayed data from multiple tables, when a child table had not yet been populated for that customer. The table without related data had NULL in the shared column, and so was overwriting the correct value from when I populated from the main table, if that makes sense. 

I also have a new command which is the opposite of POPULATE, called EXTRACT. It was really meant to work with sqlYoga record objects, so the code will reference sqlrecord_set commands, but it can easily be modified to handle normal arrays. I suppose I can put code in easily enough to discriminate between normal arrays and record objects. This is necessary because sqlYoga record objects cannot be manipulated directly without borking the object itself. You have to use sqlrecord_set commands. 

This makes it very much simpler to populate forms and extract the data from a form, because all you have to do now is create a control based upon my naming conventions I delineated in my first post, and your fields will populate automagically, as well as your arrays you use to update your sql tables. Just call populate aArray, theExclusions (a comma delimited list of columns to ignore), or extract aRecordObject and you are done! 

I hope someone finds this useful for database applications. It will save a boatload of coding later. For those who didn’t get the naming rules I will repeat them here:

Fields you want filled must begin with “fld”
Checkboxes you want hilited must begin with “btn” and the value must be true or false
Menu style buttons you want to set must begin with “mnu” and the value must be one of the choices for the menu
Radio button groups must begin with “rdo” and the value must be one of the button names in the group
Datagrids you want populated can be named as you please, and the value must be an encoded array

For instance a field named “fldCustomerName” will be populated with the value in key theData[“customername”]. A field simply named “customername” will be ignored. I use this convention because this is how I distinguish positively the fields I want populated and the ones I want to leave alone. Also, it’s how I determine the method for populating the control. 

Bob

on populate theData, theExclusions
   if theData is not an array then exit populate
   put the keys of theData into theKeyList
   
   repeat for each line theKey in theKeyList
      -- skip exclusions
      if theKey is in theExclusions then
         next repeat
      end if
      
      put "fld" & theKey into theFieldName
      put "btn" & theKey into theButtonName
      put "mnu" & theKey into theMenuName
      put "rdo" & theKey into theRadioName
      put theKey into theDGName
      put theData [theKey] into theValue
      
      if theValue is "NULL" then
         put empty into theValue
      end if
      
      if there is a field theFieldName then
         put theValue into field theFieldName
         next repeat
      end if
      
      if there is a button theButtonName and the style of button theButtonName is "checkbox" then
         set the hilite of button theButtonName to theValue
         next repeat
      end if
      
      if there is a button theMenuName then
         put lineOffset(theValue, the text of button theMenuName) into theFoundLine
         set the menuHistory of button theMenuName to theFoundLine
         next repeat
      end if
      
      if there is a group theRdoName then
         set the hilitedButtonName of group theRdoName to theValue
         next repeat
      end if
      
      if there is a group theDGName then
         try
            put arrayDecode(theValue) into theDGData
            set the dgData of group theDGName to theDGData
         catch theError
         end try
      end if
      
   end repeat
end populate

on extract @aRecordData
   put the keys of aRecordData into theKeyList
   
   repeat for each line theKey in theKeyList
      put "fld" & theKey into theFieldName
      put "btn" & theKey into theButtonName
      put "mnu" & theKey into theMenuName
      put "rdo" & theKey into theRadioName
      put theKey into theDGName
      
      if there is a field theFieldName then
         sqlrecord_set aRecordData, theKey, field theFieldName
         next repeat
      end if
      
      if there is a button theButtonName and the style of button theButtonName is "checkbox" then
         sqlrecord_set aRecordData, theKey, the hilite of button theButtonName
         next repeat
      end if
      
      if there is a button theMenuName then
         put the menuHistory of button theMenuName into theFoundLine
         sqlrecord_set aRecordData, theKey, line theFoundLine of the text of button theButtonName
         next repeat
      end if
      
      if there is a group theRdoName then
         sqlrecord_set aRecordData, theKey, the hilitedButtonName of group theRdoName
         next repeat
      end if
      
      if there is a group theDGName then
         put the dgData of group theDGName into theDGData
         sqlrecord_set aRecordData, theKey, arrayEncode(theDGData)
      end if
   end repeat
end extract