The solution I'm working on in SmartSuite is tracking data about victims of a toxic spill; these victims may become the clients of my client, which is the law firm running the litigation. The law firm is running ads that direct interested potential plaintiffs to the firm's website where they find and complete a short initial inquiry form. The form has just eight simple questions (name, email, phone, address).
Because there are quite a few of these potential clients (several thousands), the law firm I'm working for has hired a litigation support company to take the limited info we get from the initial inquiry and call those folks to additional info: mainly names and a few other pieces of personal info about other people who were in the affected household.
The litigation support company uses its own database to make calls, save info. I used a method suggested by D Mitchell in the Community to post the initial data from our solution into their system. Works great.
The problem now is how to get the data back from the litigation support company to our SmartSuite database. I can only think of one sure-fire way to do this: Have the litigation support firm send us a daily spreadsheet and do a matching-field import and update. (There's an auto-number field that can be used to identify each household that we send them, and I presume they can send that back to us so we can match on it.)
Brian Dollen suggested to me that it might be possible to use Zapier to connect Google Sheets to SmartSuite. If it were possible, I'd ask the litigation support team to create new rows in a Google Sheets spreadsheet. The zap would respond to the creation of the new row and then update the matching record in SmartSuite. I've been playing with a Zap that supposedly does exactly this for half an hour without success. There is an "update record" action in Zapier for SmartSuite. The trigger part of the zap works (it finds a new row in the Sheets doc). The update-in-SmartSuite action looks right, but I keep getting a 404 error in the action. Has anybody used this combination of trigger-and-action to build a zap that updates a SmartSuite record when a new row is added to a Google Sheets doc?
Other thoughts? My main question is, how can I update an existing record in an app in SmartSuite with data coming from outside? We don't have matching forms (yet) in SmartSuite and in any case I don't think that would work. I'm pretty sure that the litigation support form — which will be returning hundreds and hundreds of updated records — does not want to have to fill out individual forms.
Thanks in advance.
William