Export Manager - Join tables/forms

[det]

Is there a function in form tools that allows me to combine or join data from multiple (or all) forms which can be exported as one file, or am I going to need to go rummaging in the database myself? I've had a look around the documentation and fora - can't seem to find anything on this subject.

Basically the situation is that I've got multiple forms with form fields in common which need to be aggregated to come up with some statistics regarding feedback. Things like ratio of male respondents to female, location, etc.

ps. if it's not an option normally, i humbly submit it as a feature request.

Thanks for any and all advice in advance.

[Ben]

Hi Det,

I was just apologizing to another chap - The "new posts" icon wasn't showing up for me on this forum and I didn't see the last few posts. Just playing catch-up...!

This is something I've wanted to add for some time, but it's REALLY non-trivial and I haven't figured out a good approach yet. So yes, it'll have to be logged as a feature request for now.

Sorry I couldn't help -

Ben

[det]

Hi Ben,

Thanks for the (albeit too late for me) reply. I've written my own solution to the problem, and maybe my solution can benefit you for your approach to this problem in the future.

I'll start by saying I'm not a great programmer and I'm aware of how inelegantly I've likely written and solved this, but nonetheless:

Basically in order to 'link' the forms to one another, I've given them a certain prefix in the name (eg. CTP_). Although were this a feature it'd probably be better off as a separate optional field stored in each table. Using MySQL, I searched for all form_id's where the name contained that prefix and returned the names of the associated tables (ft_form_x). In PHP a new csv file is created in order to store the results, and because the data is common to all these forms, created an array with the name of all the relevant fields I want as the first line of the csv. For each ft_form_x, I select all the relevant data from each form, shove it into the array, append it to the csv, rinse and repeat until done. I've managed to narrow down the data returned using the submission date to create csv files for each month.

It might not be elegant, but it works. Thankfully I only need to do this for 2 sets of forms, so having a set of hardcoded prefixes in the PHP isn't a problem (yet). I've set up the crondaemon on my server to run this script once a month, save the csv into my root directory and also email a copy of the full collection and a copy of the previous month's responses.

Hope this is in some way useful to you, you're free to have a copy of my revolting but commented PHP if you would like.

Thanks for such a useful tool though, you've made my job at least in one regard a good deal easier.

[Ben]

Nice! This may well help out other people, so thanks very much for posting your solution - very good to know you go it going.

I think I get the gist of your solution, but later on when I look at this problem myself I may fire off an email to you for your code!

All the best -

- Ben