Inserting new records in SyteLine is not just limited to the Mongoose tool-set or SQL layer we can also use form script to add new records via the IDO layer.
To do this we need to define two variables the first of which is an update collection variable which will hold the records we want to insert whether this be one or many records with this variable being of type
UpdateCollectionRequestData, and on initilisation of the new object variable we are also able to specify what IDO collection we want by passing in the IDO name as an argument.
In the below example we are using form script to insert a new family code and in our implementation we have called the new collection variable
oUpdateCollection and have told it we want to update the family codes IDO which is called SLFamCodes.
Dim oUpdateCollection As New UpdateCollectionRequestData("SLFamCodes")
Secondly we also need to create a variable of type
IDOUpdateItem which will represent a single record in our collection to which we are able to define the properties and values we want to insert and when initialising the object we are able to specify what action type we want the update item variable to be, in our example as we are inserting new records so we have set this to be
Dim oUpdateItem As New IDOUpdateItem(UpdateAction.Insert)
IDOUpdateItem types are part of the
Next we can add the properties to the
oUpdateItem variable that we want to include in our insert. We do this by calling the add function and passing in the property name, property value and lastly whether this property should be marked as modified which we will set to true as it will be a new record.
For our family code example we want to insert the new family code along with the family code description which have the property names FamilyCode and Description respectively.
oUpdateItem.Properties.Add("FamilyCode", "ABC1", True) oUpdateItem.Properties.Add("Description", "New family code desc", True)
Once we have added all the properties we need to the
oUpdateItem variable we then need to add this object to the collection update variable
If we needed to add more than one new record then we could repeat the
oUpdateItem process by instantiating a new object of type
IDOUpdateItem adding the required properties and property values and then lastly we would need to add the new update item to the update collection variable so that all the records could be processed and sent to the database in a batch rather than individually.
Now that we have the new family code records in our
oUpdateCollection variable we can update the database, we do this by passing our collection to a shared update collection2 method which uses the current session context to make the IDO and then database call.
If we have defined our IDO name, property names and values correctly the records will now be in the database, if any errors were encountered during the insert then the method call will return an error along with an error message, like you would get when doing an insert direct in SQL.
The very last thing we need to do is to clean up the variables we used by setting their references to nothing.
oUpdateItem = Nothing oUpdateCollection = Nothing
Full code snippet to insert a new record into a SyteLine table via the IDO layer and form script.
Dim oUpdateCollection As New UpdateCollectionRequestData("SLFamCodes") Dim oUpdateItem As New IDOUpdateItem(UpdateAction.Insert) oUpdateItem.Properties.Add("FamilyCode", "ABC1", True) oUpdateItem.Properties.Add("Description", "New family code desc", True) oUpdateCollection.Items.Add(oUpdateItem) Me.IDOClient.UpdateCollection(oUpdateCollection) oUpdateItem = Nothing oUpdateCollection = Nothing
Mongoose.IDO.Protocolnamespace is imported for you when using a SyteLine form as part of the implementation so does not need to be explicitly referenced.↩
Me.IDOClient.UpdateCollectionis a shared method that is built into the Mongoose tool-set, all we need do is pass our collection variables into it and there is no instantiation required of the method itself.↩