ContributionsMost RecentMost LikesSolutionsRe: QuickBase as CRM We are currently working on integrating the HubSpot company and contact objects to equivalent tables in Quickbase. HubSpot does a few things under the hood, such as record merges and associations (two-way user-controlled relations) that we've had to simulate in Quickbase, but no real roadblocks so far. ------------------------------ James Calloway ------------------------------ Re: online payment option No, we're connecting to HubSpot. PayPal security may reasonably be stricter than HubSpot, so I can only walk through how we handled it. First of all, even though we use Webhooks with HubSpot, we don't actually use the Webhooks Channel. We found we can do what we needed with the JSON Channel. For us, the outgoing connections from Quickbase to HubSpot are where we use OAuth. The first step was to set up the initial authorization. For HubSpot, that involved "creating an app", which meant registering Quickbase as our "app" and identifying which parts of the HubSpot API we wanted to grant Quickbase access to. This generated an authorization URL for that app, along with a Client ID and Client Secret, which we used to get an Access Token and a Refresh Token by logging as a HubSpot user having all the access we were authorizing for the "app" and manually loading the authorization URL in the same browser. This was the part that involved the OAuth redirect. The authorization URL went to a page that asked us to select the appropriate account and returned us to the redirect URL with the two tokens added to the redirect URL as query strings. We manually copied them both for use in our Pipelines. In our case with HubSpot, the Access Tokens only last for 30 minutes, so each outgoing pipeline refreshes the token by making a Fetch JSON step to POST the Client ID, Client Secret and Refresh Token to HubSpot, which responds with a new Access Token, which can be parsed out with an Iterate Over JSON Records step. After refreshing the Access Token, we use additional Fetch JSON steps to connect to the various endpoints documented for the API. For incoming updates, we set up an Incoming JSON Pipeline. That generated an endpoint URL that the other side uses for sending Webhooks event notifications.This may be where you run into trouble with PayPal, because the only security modes that the Incoming JSON step supports are JWT, which we have yet to find any other system that uses, and the old HTTP Basic. (The Incoming Request step in the Webhooks Channel, by the way, supports only JWT.) The only other option is no security, so if PayPal even permits sending Webhooks notifications without security, you'll have to assess the risk level to your Quickbase application. Assuming you can use the Incoming JSON step, the next step in the Pipeline is to Iterate Over JSON Records to interpret the notification. We couldn't find any documentation in HubSpot for the format of the notifications, so we had to trigger events and intercept the notifications to figure out a JSON schema sample that would parse them. From there, we use whatever steps are applicable to create, update or, if you permit deletions, delete records. I hope this helps, and in any case, good luck with your project. ------------------------------ James Calloway ------------------------------ Re: online payment option OAuth isn't always a problem in Pipelines. We're currently connecting via OAuth to an external service, and also did so with a previous external service. The implementations of OAuth differed between the two exernal services, so "results may vary". In our current usage, the redirect URL was necessary only for the initial set up and whenever we changed authorizations, which for us hasn't happened often, so we did that manually to harvest the initial token and refresh token. From there, it was easy to automate it in Pipelines going forward. The previous external service did not require the redirect URL at all. ------------------------------ James Calloway ------------------------------ Re: Remove totals column from summary report Agreed. In some cases the totals column(s) in a summary report are simply incorrect, such as when reporting return on investment or certain other percentages. Those cannot simply be totaled. They have to be recalculated based on the aggregated data. ------------------------------ James Calloway ------------------------------ Is there a way to prevent Bulk Upsert from clearing a field if there is no data? I have a table in Quickbase that is intended to be maintained as the image of a similar table in a foreign system, and I am attempting to use Pipelines to keep the table updated in near real time, rather than to batch load it. When changes occur in the foreign system, it sends signals to a Quickbase webhooks URL. The signals can be of type "creation" or "fieldChange", among other types. Each change to a field results in a separate signal, and when a record is created in the foreign system, the system first send sends the "creation" signal, then sends separate "fieldChange" signals for each field that gets populated during creation. The signal looks something like this: { "eventType": "fieldChange", "recordId": 16793426360, "fieldName": "company_name", "fieldValue": "Acme" } Since any given signal can be a change for any field in the foreign table, I use the following Jinja code for every field in the Quickbase table that might need to be updated: {% if b.field_name == "company_name" %} {% if b.field_value == "" %} {{CLEAR}} {% else %} {{b.field_value}} {% endif %} {% endif %} This works perfectly if I use Update Record. The field referenced by the signal is updated and the other fields are not touched. However, I run into trouble using Update Record when a new record is created. The signals come quickly, and because Pipelines run asynchronously, the "creation" signal isn't necessarily the first signal to be processed. To avoid losing data, I tried checking to see if the record exists before processing a "fieldChange" event and then creating the record if it does not. The problem is that occasionally, between the time one Pipelines thread gets a "does not exist" for the record and the time it attempts to create the record, another thread will already have created it, and the former thread errors out with an error 51 for trying to create a record with the same unique key as an existing record. So then I try Bulk Upsert. I actually had started with Bulk Upsert, but forgot why I abandoned it until I tried it again: When I use the exact same Jinja code in the fields for Add a Bulk Upsert Row, it clears every field that is not referenced by the "fieldChange" signal (whereas Update Record ignores them). I could add a step to look up the record in question and re-enter the field value, but that runs into the occasional problem of the record not existing yet. I'm hoping that there is a way to instruct Add a Bulk Upsert Row to ignore the field (rather than clear it) if there is no data for it. Alternately, if there is another approach that might work in this scenario, I would love to learn about it. ------------------------------ James Calloway ------------------------------ Re: Changes to Quickbase feedbackMy mistake. It's a tiny tab on the left edge of the page.Re: Changes to Quickbase feedbackStill not seeing any Feedback tab or link on the My Apps page. Meanwhile, the User Voice instance says it is no longer available.