How do I.....Input data from Web form, save as csv (?), export into PDF fields...

OCST

Sunny von Bulow
SoSH Member
Jan 10, 2004
24,483
The 718
Hi.

I'm the junior partner in a two-partner firm, which means I get to do all the admin stuff.

I want to automate our client and matter intake process. You can think of matters as children of clients: every client has at least one matter associated with him/her/it, and sometimes more than one; every matter is associated with one and only one client.

Here's what I want to do. This starts when there is a potential new client/matter:

1. Input relevant info into a Web form. There is a workflow:
--if existing client, go directly to matter intake
--if new client, enter client info (name, address, email, phone etc). - mostly text fields, a couple of dropdown/radio button fields, then to matter intake
--matter intake is also mostly text fields, with a few dropdown/radio button/text box fields.

From my playing around, I think that Google Forms can do this.

2. Save the input into csv files, Clients.csv and Matters.csv. Again, I think Google Forms can do this, although it seems to want to use that annoying Google faux-Excel product.

3. Export from the csv file into fields in a PDF that are mapped to the CSV file (name, address, etc). The PDF is our firm's retainer agreement form. A few of the fields need to have some simple if-then logic based on the contents of the csv, ie if Field X on the csv is value 1, then I want text block 1 to appear in the PDF; if it's value 2, then text block 2, etc.

4. Auto-magically email the PDF to the client, bccing an email account I have set up to keep track of what's gone out.

For steps 3-4, I think it may be easier to do a Word mail merge and convert to PDF, but I think (??) that PDFs can import data, and this is where my first-hand knowledge gets hazy and I'm here for help.

5. Upon the client returning the retainer, ie the client has hired us, import the matter info and the client info (if a new client) into our practice-management software. This is web-based and the import process is simple; I think it would just be a matter of isolating the pertinent row of the appropriate csv.

5a. Ideally, if after x days, the client hasn't hired us, email the client a declination of representation form letter, importing again from the csv, ie you never hired us so don't blame us for your legal problems.

As I get further into this workflow, I start to wonder if some database product other than Google Forms front end plus ??? back-end might be better, but again I'm out over my skis now and that's why I'm asking for help here. About 20 years ago I was a pretty good Lotus Notes/Domino developer and I think I could do all this in Notes, but I'm guessing that Notes is dead and/or that a license would cost money.

Thoughts?

Thanks everyone.
 

InstaFace

The Ultimate One
SoSH Member
Sep 27, 2016
21,754
Pittsburgh, PA
1) You want two Google Forms, one for Clients and one for Matters. You can give the latter out to clients who complete the first one (e.g. on the thank-you message), or send them the link to it directly.

2) Google Forms can save their results to a Google Sheet (the faux-excel thing you're talking about). For your purposes, Google Sheets is perfectly sufficient - it stores data in a tabular format. You can export them to CSV as often as you like, and because you're just reading the data rather than writing to it, there's no version-control concerns.

3) PDFs suck. They suck technologically, data-wise, really the only feature they have is that most people can't edit them. You can always save a Word or Google Doc into a PDF but there's really not a ton of incremental value to having it in a PDF.

4) Yes, you want a mail merge. There are add-on products, some of which cost money, which do mail merge with Google Docs. This is probably what you want, though I'm not going to search for them right now. I've browsed them before, because I manually send ~100 emails about 4 times per year that I'd much rather be mail-merged, but didn't feel like spending the money to buy the software product. So you want to do this through Google Docs.

5) Now you're talking some more complex logic that would require some coding. It's not a particularly deep task to fetch and filter data from a Google Sheet and then push it to some 3rd-party software, but the obnoxious part will be recognizing the triggering event (i.e., we've received an email with a signed form, AND a corresponding payment, and it's the right amount, and I can recognize the payee, and it's cleared, and the signature is in the right place, blah blah blah) that would do that. I think it's going to be better to have a person decide on the initiating event, but you can still enable some easy clicking for you / your staff to trigger moving that information over.

5a) Lotus Notes / Domino is a tool of the devil, and my bet is that you haven't paid up for all your necessary exorcisms from the 90s. Perish the thought. You can teach yourself MySQL or Postgres (for the database backend) and some web scripting language like PHP (for the interaction logic and display logic, rendering to HTML) if you want this all to be web-interactive; other more robust tools like Python would be overkill for this. Yes, that will cost money, depends on whether you'd find it fun to learn about it yourself or worth spending $5-10k to a freelancer to build it for you.
 

Blacken

Robespierre in a Cape
SoSH Member
Jul 24, 2007
12,152
If you used Notes, I'd find you and leave dog poop on your lawn.

Disclaimer: I work at IBM.
 

Blacken

Robespierre in a Cape
SoSH Member
Jul 24, 2007
12,152
Probably, but there'll be part of you you never get back. Again.

I'd basically do what @InstaFace suggests. Ordinarily I'm careful about that stuff but you'll own it and it'll be on your own head, so go nuts.
 

Max Power

thai good. you like shirt?
SoSH Member
Jul 20, 2005
7,878
Boston, MA
If you want an out of the box product, check out https://www.intelledox.com/ with their DocuSign integration. I got a demo of it from DocuSign a while ago and worked basically like online tax services. You answer questions in a series of forms and it generates a PDF in the end with the relevant information. And since it links into DocuSign, you can get electronic signatures and countersignatures on the form with whatever workflow logic you define.