Editable Table - get error when testing (dashboard)

The "COMMIT TRANSACTION" failed error is likely if the previous SQL statement did not fire at all, or fired but the SQL had an error?
The error above is saying it was trying to perform a commit rule, but it had not performed a sql update to the database?
First thought would be to check the SQL logs just before the error and see if it was successful or not.
If there is no SQL update log, then something is wrong with a data it is trying to match on.

Hi mistalla,

In order for the delete to happen correctly it needs to store a record of all the rows that have been removed by the user on the screen so that the correct SQL can be fired when they hit save.
This wont happen automatically when you add an editable table control, but is relatively easy to set up, and has already been done in the example project I posted before.

As you have noticed, there is a deletedRows hidden field, which is used to store the list of ids of the deleted rows.
This is done in the change_function defined for the editable table. If you go to the field details tab in FormMaker, and look at the table_init field, you should see that the change_function for this table is set to processRecordChange. This is a custom script function that is stored in the PageScripts.js script file which you can access from the View Scripts link in the top right.

This function processes every delete change, and adds the CID (contact ID) value to the deletedRows hidden field in an XML formatted string.
This function is passed the row ID of the table row being deleted, which is made up of the repeat name, the row number, and the name of the group. Therefore to find the ID of the field containing the CID value we want, we first remove the group name from the end of this id, and then add on the name of the field we want to get.

The next area to look at is the rules in the UpdateRecords controller.
The Process deleted rows rule just takes the string value submitted from the deletedRows hidden field, and converts it to a proper XML structure to easily process.
This processing happens in the Delete_contact_database_entry_for_action_update rule, which loops through each entry in the this newly created structure (see the for_each_incoming_record condition) and then fires the relevant delete SQL query to the database.

I hope this makes sense. :)
Let me know if you have further questions on this.

"If you go to the field details tab in FormMaker, and look at the table_init field, you should see that the change_function for this table is set to processRecordChange."

Does this show up in the first screen "before" I open the PageScripts.js script file?

Yes. Go to the Field Details tab, and then selection the table_init custom field using the tree on the left.

This should show a text editor in the middle of the screen which contains a script fragment. This is the script that initialises the editable table display.

There are a few different parameters set here, but the last one should define the change_function property for the table:

I'm glad you managed to get it working.

Let us know if you have any further questions.

Hi Laura

If you have the delete part working correctly, then it shouldn+?+?+?t be too hard to get the inserts and updates to work, as this is a simpler process.
It is important to remember that the editable table control only provides an XML fragment of the data to the controller. It is up to the rules to determine how this data should be mapped to the required SQL calls.

If you look at the page structure for the Records page in the example project, you can see that there is a hidden CID field at the end of the table. For existing records this stores the ID of the contact. For new records this will be blank, as the user will not be able to enter a value in the hidden field. Therefore we can check whether this value is present or not in our rules to determine which records to insert or update.

As jrobert mentioned earlier, you get that SQL error if it has tried to commit without first doing any kind of update/insert/etc transaction that needs to be committed.
It is the conditions on the rules that determine whether they fire or not, so if the SQL in your rules is correct, I would take a look at the conditions on the insert and update rules.

In the example project, the Update_contact_database_entry_for_action_update rule performs the SQL updates, and this has two conditions. The first simply checks that the action called from the page was correct (+?+?++update+?+?+? in this case). The second condition is the important one. This is a looping condition which means that all the actions in this rule will be fired for every element in the data matched by this condition. The condition XPath matches every record element in the XML fragment returned from the editable table, and then has a predicate (section in square brackets) to restrict it to just the records where the CID value is not empty.
This means that an update SQL query will be fired for every record that has a CID value.

The conditions on the Insert_contact_database_entry_for_action_update rule are very similar. The only difference is that the predicate section for the looping condition is the opposite to that for the update rule (uses the not() function). This means that this insert rule will be fired for every record that doesn+?+?+?t match the update one, ie doesn+?+?+?t have a CID value.

I hope this makes sense.

The best option is probably to attach an export of your project.
Alternatively (to just send the rules), you could attach the relevant rules file from the 'logicsheet_pool' directory for your project in the repository.

Yes that is likely to be part of the problem.

As these rules have a looping condition, this sets the context point in the data that the XPaths for each action will be evaluated from. Therefore the XPaths for the actions (including the SQL parameters) should be relative so they pick up different data values each time.
(See http://www.hyfinity.net/mvcdocumentation/XStudio/RuleMaker%20User%20Guide/Loop%20on%20this%20Selection.html)

Hi Laura,

It does seem like some of the XPath bindings for your fields and rules are not in sync which may be why this is not working correctly for you.
Can I ask how you created the table on your page? Did you drag on all the fields from the database or from the design palette? Did you get the popup asking which actions to associate the data source (database) with? If so, do you know if your action was selected?

Normally (with the database correctly mapped to the action) the format of the submission data for your page will be set up so there is an element under 'mvc :D ata' based on the table name, which then contains a number of 'record' elements for each row in the table. You would then get a submission XPath for the repeat component of '/mvc:eForm/mvc :D ata/<table name>/record', and each individual field would have a relative submission binding from this.

If the fields were added from the palette, or the database was not linked to the action, then all the bindings will map to the formData section instead.

For some reason you seem to have a mixture of the two!

Regardless of what structure has been generated though, you can manually adjust this as needed to make sure you have the correct data format.

Go to the Data Bindings tab for your page, and select the Action Submission Bindings tab.
Select the accordion pane for the action you want to configure (I think this is 'update' in your case)
Have a look at the document shown on the right. Does it show the correct data fragment for the database under /mvc:eForm/mvc :D ata?
If not, use the 'Edit' button to open up the document, and add in the appropriate fragment under the mvc :D ata element. I think in your case it should be something like:

<DeviceAccessory xmlns=""> <record> <deviceType/> <deviceSubType/> <dateAquired/> <activationDate/> <make/> <model/> <registeredName/> <uniqueID/> <serialNumber/> <deviceStatus/> <notes1/> <notes2/> <notes3/> <deviceID/> </record> </DeviceAccessory>

Now click on the repeat on the left, and adjust the submission binding accordingly. eg to something like '/mvc:eForm/mvc :D ata/DeviceAccessory/record'. You should see the match with the updated document.
You can now select each of the fields within this repeat and adjust their submission bindings to be relative to the repeat context by dragging them from the relevant element in the document on the right.

The final step is to adjust the rules so that they are correctly processing the new data structure. From looking at the file you sent through previously I think the only change you will need to make is to the SQL parameters. These all need to be relative XPaths (to the looping condition) eg 'deviceType', 'deviceSubType', etc. Again you should see them matching against the updated document on the left hand side.

Hopefully if you now generate and deploy the application it will work correctly.
If not, you can use the dashboard to see what is happening. You can look at the incoming message to your controller to make sure the data submitted form the page is now in the correct format, and you can open up the additional details to see what rules fired and how they affected the data.

I hope this helps.

The basic approach to setup an editable table linked to a database is:

When you generate the application this should now give you the correct bindings and data structure from the page, and will also generate you a SQL rule in the controller for the one operation that was chosen in the popup.
In reality, you are likely to want rules for all the SQL operations, but in WebMaker 3.1.2 you are only able to select one operation per action to be automatically generated. Therefore you will need to manually setup the other rules, or create temporary additional actions for the other SQL operations and then adjust the generated rules accordingly.


It is important to know that this process only helps to generate the correct rules and bindings for you. It is still possible to set this all up manually (or adjust the generated details as needed) as I mentioned in the last post.

At the end of the day, each page submits an XML document to the server, as defined by the details specified on the Action Submission Bindings tab for that page. The structure of this document (under the mvc :D ata element) can be what ever you like. WebMaker just makes some assumptions for you based on where the fields came from (eg database or palette) to give you the default formData or table name structures.
The controller then applies the rules defined for it to process this XML document and make the required SQL calls etc.

As long as the structure of the data submitted from the page (as can be seen for the running application using the dashboard) matches what the rules are expecting it should work ok.

I hope this helps.

That sounds like the deviceID hidden field has a mandatory validation constraint on it. By default the editable table will ensure the new values pass their validation constraints before letting you add a new row. You can check the constraints for a field on the Field Details tab under the Data Constraints section.
As this is a hidden field that the user can never change, the best option is probably to stop it being validated at all. You can do this by setting the Perform Client Side Validation on this Field? option to false under Hint and Error Options on the Field Details tab.