Custom form to Data Record?

wardy · 2013-06-11 02:10:48 UTC

Hi guys

Has anyone attempted to create a new Data record (not recordset) on a custom form submission. The client requires a pretty comprehensive dashboard built for a custom form I'm building them. They want to be able to search (I'll use squiz search) and filter by various settings, date range, form field data.

I know I can build an asset listing for form submissions, which in most instances is ok, but I also need search and filter parameters from different for fields.

I'm thinking the best way to manage the data would be to create a data record for each submission, but is it possible? The client currently gets around 100 paper based submissions per week.

Cheers

nnhubbard · 2013-06-11 15:34:19 UTC

I'm thinking the best way to manage the data would be to create a data record for each submission, but is it possible? The client currently gets around 100 paper based submissions per week.

Why are you thinking this is the best option?

wardy · 2013-06-11 23:40:26 UTC

I guess I've not tried to yet, but I want to be able to use squiz search on the submission records. As mentioned, there's about 100+ submissions per week, but the form is only going to be used by round 300 registered organisations, the administrators need to be able to print a list of one organisation, or many. I know it's easy to use an asset listing to read the data, but can I use an asset listing to filter out particular entries, or display a date range?

Plus there may be a case where the administrators need to edit the submission.

If this can be done using asset listings, then I'd be pretty happy to do that. I just thought form submissions were like a shadow asset, where all you can do is list them.

nnhubbard · 2013-06-11 23:43:58 UTC

If this can be done using asset listings, then I'd be pretty happy to do that. I just thought form submissions were like a shadow asset, where all you can do is list them.

Yeah, submissions can be edited. You just don't see them in the Asset Map because they are TYPE_3.

Doing the filtering though wouldn't be as easy, so like you said a search might be best.

wardy · 2013-06-12 01:04:51 UTC

I don't want the admins to have a squiz backend user account. I want to create a member type website for them, so they won't have access to matrix or easy edit/edit plus. That's why I was considering using data records, I know data record assets can easily be updated, as all the info is stored as metadata, giving us quite a powerful way of using the data.

If there isn't already a way of posting form submissions as new data records, then maybe I'll pop the idea in roadmap and see if labs can create a trigger of some sort.

joel · 2013-06-12 01:26:53 UTC

Setting up a database table, using the 'run database query' submission action on the custom form to insert your data into it & then pulling it back in through a DB datasource might be a better route if you want powerful listing/query options.

wardy · 2013-06-12 01:37:48 UTC

Do I need an actual database do use this functionality? and what's the learning curve like to implement this?

nnhubbard · 2013-06-12 02:57:00 UTC

Do I need an actual database do use this functionality? and what's the learning curve like to implement this?

Yes. You would need to know who to write DB queries.

markn · 2013-07-11 06:42:59 UTC

An alternative is to use a data record asset builder instead of a custom form (ie the form the end user sees is actually an asset builder)
You can use the "Created" body copy as the "Thank You" page.

You can also put a "Create location" option in the "Form" (ie asset builder)

This obviates the need for a seprate database.

Anthony · 2013-07-15 21:13:42 UTC

I'm also hoping to turn submissions into data records. In my case its for two reasons... (1) I need to associate a metadata schema with them so I can record extra information that the submitter of the form doesnt enter and (2) I want to be able to link them into multiple places to facilitate various listings.

I'm not at all familair with the format of the Squiz database... is there any hope of a database query that can run in the actually create the data record assets? I guess it would have to be a series of pretty complex queries and pretty risky if the database format ever changed?

I also wondered if theres any way the REST submit actions could be persuaded to post in the right format to an asset builder, or whether the SOAP action could be made to talk to the squiz SOAP server to call the create asset and set metadata functions?

Failing any of the above I think I could make it a semi manual process where someone visits a page generated by an asset listing that fires a load of JS code using the JS API to create the records.

Open to any other suggestions and interested to hear from the original poster how you got on?

nnhubbard · 2013-07-15 21:22:14 UTC

So, you are not able to just use an Asset Builder for this whole process so you don't have to worry about starting with a Custom Form and then having Data Records created? You can't just have an Asset Builder create those?

If not, have you looked into a trigger that would create a Data Record with the information created in a Form Submission?

wardy · 2013-07-16 04:33:40 UTC

I had a pretty complex custom form with about 50+ fields on it. There's a lot of JavaScript and jquery on the form, plus some tricky validation. For me there really wasn't an option to use an asset builder.

Then I needed to build a members dashboard where the form submission can be searched, listed, sorted, counted, and some data edited. As far as I know, the submissions couldn't be searched, so I had to find a way of making the submissions searchable. That's when I read the squiz manual section about DB assets. DB assets are essentially blank pages with metadata applied; so I thought why not just apply the metadata to the submission? same thing.

I found out that I only needed to search, display, and edit about 12 of the field values, so I created a metadata schema and applied it to the submissions folder, then I have a trigger for each of the fields I needed, which populates the metadata on form submit.

Such an easy solution to a very complex requirement.

On hindsight I'd love to do this using JS API, but Squiz tutorials are pretty none existent, and the manuals are often very vague at best. Plus stuff like SOAP, REST, JS API's, and all the other goodies that would really help, are not covered in any of their training. Maybe there's a case for someone who knows this stuff to set up an independent training consultancy focused on getting the best out of Squiz.

nnhubbard · 2013-07-16 04:38:46 UTC

On hindsight I'd love to do this using JS API, but Squiz tutorials are pretty none existent, and the manuals are often very vague at best. Plus stuff like SOAP, REST, JS API's, and all the other goodies that would really help, are not covered in any of their training. Maybe there's a case for someone who knows this stuff to set up an independent training consultancy focused on getting the best out of Squiz.

What questions do you have about the JS API? It is very powerful and the manuals do a pretty good job at explaining everything.

Would be happy to consult with you if you are looking for someone.

joel · 2013-07-16 05:58:51 UTC

(2) I want to be able to link them into multiple places to facilitate various listings.

I'm not at all familair with the format of the Squiz database... is there any hope of a database query that can run in the actually create the data record assets? I guess it would have to be a series of pretty complex queries and pretty risky if the database format ever changed?

Form submissions can be linked into multiple places, however I think the only parent they are allowed to have is a folder asset. If you link them around with type 1/2 links then you can even see them in the asset tree. That said.... I would strongly advise against doing this - we ran into some troubles with submission assets we had linked around the place when we had an upgrade done a while back (this might not be the case anymore though, I dunno). They're also kinda hinky assets, with lots of stuff linking them back to their parent forms.

As far as a db query to create assets goes - you're asking for trouble there.

Nic's suggestion of an asset builder is the best course of action if you want to use data records.

wardy · 2013-07-16 06:08:27 UTC

Thanks Nic, I guess just looking at them is pretty daunting for a guy who doesn't have much experience. I mean, the trigger was so easy to figure out. How do I replace my 12 triggers with one simple JSAPI? Is there coding involved, where does the coding go? Do you have to custom code, or can this stuff be done using keywords?

Ok, as an example, my dashboard is almost half finished, I have the search and listings working fine. I did have the complete data from the form opening in the original form so it could be edited and saved. Now I've been told by the client they don't want the original data edited as they need this for an audit trail. Cool! Then I'll just use the metadata for editing purpose.

My dilemma is what to use? It's a dashboard completely built on the client side with a member login. I need to load the metadata values into text boxes, allow them to be edited and then saved. Do I use JS API to save the values back into the metadata? Or do I just create triggers again and have the trigger update the new value. Again, the trigger would be the easy cop-out, but I'm sure there's a leaner method for this.

What I was talking about earlier, you know tutorials, it would be great if people who have done this before, maybe do some screencasts as tutorials, of various method or techniques, such as using web services. These could be posted under the tips and tricks section of this forum.

nnhubbard · 2013-07-16 06:20:28 UTC

Thanks Nic, I guess just looking at them is pretty daunting for a guy who doesn't have much experience. I mean, the trigger was so easy to figure out. How do I replace my 12 triggers with one simple JSAPI? Is there coding involved, where does the coding go? Do you have to custom code, or can this stuff be done using keywords?

Yes, there would be a good deal of coding involved, obviously in javascript. If you are not familiar with javascript or programming it might not be a good choice.

There are some parts of your Dashboard that could benefit from the JS API such as replacing your triggers and updating the metadata fields. Sometimes the JS API is nicer and cleaner for these sort of operations.

Here is a simple example of how the JS API could list children of an asset:

// Create new api object and set key
var js_api = new Squiz_Matrix_API({key: '1348815799'});
js_api.getChildren({

asset_id: 86,

levels: 1,

type_codes: [‘page_standard’],

dataCallback: function(data) {
  $.each(data, function(key, val) {

     $('body').append(key+' =&gt; '+val.name+' &#91;'+val.id+']');

  });//end

}//end

});//end

Anthony · 2013-07-16 11:32:25 UTC

Hi Nic,

have you looked into a trigger that would create a Data Record with the information created in a Form Submission

I wondered which trigger you meant... hadnt realised there was a "Create Asset" trigger action... I've just looked and... so close but not quite! Aargh! As far as I can see that trigger action lets you set the essential attributes of the asset being created, but has no provision to set arbitrary metadata fields on the new asset. Looking at the manuals it doesnt seem thats an option even in the latest version. There's a "set metadata" trigger action, but I assume that could only set metadata on the asset that fired the trigger (the submission) not the new asset just created by the previous action.

What do you reckon it would take to enhance the "Create Asset" trigger to allow setting of metadata as well as attributes?

I dont think using an asset builder instead of the form is really viable for me at this stage. I'm making use of all the various submission email notifications etc which would presumably have to be replicated with triggers etc. Plus the confirmation page, validation etc. Generally the form itself is working pretty neatly.

I hadnt realised I could apply metadta schema to the submission folder - obvious when you think about it ! - so that might be one I will explore further. There's ways I could work around the desire to multi-link the records. But I'm pretty confortable with the JS API so I'm going to persue that too. At one point I thought I could stick some JS in the "thank you" page... but I presume the public user wont have write access to the submission asset they just cretated so any calls would fail.

nnhubbard · 2013-07-16 16:05:46 UTC

What do you reckon it would take to enhance the "Create Asset" trigger to allow setting of metadata as well as attributes?

I hadnt realised I could apply metadta schema to the submission folder - obvious when you think about it ! - so that might be one I will explore further. There's ways I could work around the desire to multi-link the records. But I'm pretty confortable with the JS API so I'm going to persue that too. At one point I thought I could stick some JS in the "thank you" page... but I presume the public user wont have write access to the submission asset they just cretated so any calls would fail.

Yeah, it might require a tweaked trigger, so I am not sure you can do what you are wanting.