Forum Discussion

BrianCafferelli's avatar
BrianCafferelli
Qrew Captain
4 years ago

Streamline your procurement process using the Formstack Documents channel

Streamline your procurement process using the Formstack Documents channel

Formstack Documents enables anyone to easily create smart documents, upload important data, and streamline the paperwork process. Skip manual documentation and data entry with a smart document that can be easily merged with relevant data which is automatically formatted.

With the Formstack Documents channel you can auto-populate documents in PDF, Word, and PowerPoint formats from data in Quickbase apps. This allows employees to easily gather data from across disparate systems to better manage and execute the document creation process. With automated data merges and uploads, Pipelines helps to eliminate tedious steps in the paperwork process and ensure that documents are up to date.

Here are just a few types of documents you can generate using our Formstack Documents channel:

  • Contracts and agreements: create offer letters for job candidates, rental leases, and other types of contracts and agreements
  • Pitch decks and glossy proposals: dynamically merge your latest marketing copy with your glossy Powerpoint pitch deck so your sales team always has the latest and greatest messaging
  • Supplier procurement / invoicing: build invoices, purchase orders, and procurement forms to send to suppliers, partners, or customers.

Read on for an in-depth example of how the Formstack Documents channel can help you streamline the procurement process.

Don’t have a Formstack Documents account yet? You can visit their website to sign up for a free 14-day trial.

 

Example use case

Imagine that an auto manufacturer uses Quickbase to track their inventory of parts. Their procurement team receives a design document whenever a new car model is ready. The document lists each part needed to build the car. The procurement team figures out how many pieces to order, for each part, and writes up a purchase order for their supplier.

To streamline the procurement process, their suppliers require that a standard PO form be submitted as a PDF file. Currently, the procurement team manually builds each purchase order, saves it as a PDF, and then emails it to the appropriate supplier. Since this is a manual process, there have been delays in sending out the purchase orders as well as some missing parts.

The company also uses Formstack Documents, so they’ve decided to automate this process using Quickbase Pipelines. The plan is to build a pipeline which will take the purchase order and its line items in Quickbase and send it to Formstack to generate the PDF. Formstack will then email the file to the supplier.

You can see what the purchase order will look below. Next you'll learn how to build your own.

 

  

Creating the document template

Before thinking about the pipeline, you’ll need a document template where Formstack can fill in your purchase order details. You’ll design and lay out your form in Word, including what Formstack calls “merge fields” anywhere you need data to be filled in dynamically. See the image below for the format you’ll need to use:

 
 

Somewhere in the middle of your document, you’ll want to display the list of all line items included in the purchase order. To do this, we’ll use the Formstack tablerow feature as seen below:

Item No

Description


Unit Price

Quantity

Line Item Total

{tablerow from=$items item=_lineitem} {$_lineitem.item_number} 

 

 

{$_lineitem.description}  

 

{$_lineitem.unit_price} 

 

{$_lineitem.quantity} 

 

{$_lineitem.line_item_total} 

{/tablerow} 

 

 

NOTE: Within the list, the name of each attribute must match the name of the Quickbase field you’re using to populate it. The merge field’s name can’t include spaces. If some of your field names do include spaces, replace each space with an underscore in your document template. For example, if the name of your field in Quickbase is Line Item Total then the name of the merge field within the list should be {$_lineitem.line_item_total}

 

Next, you need to upload your template. To do so, open Formstack Documents and click Documents in the toolbar at the top of the page. Next, click New Document and follow the prompts to import your file. When prompted to choose where the data will come from, select Integrate with an external service.

 

Setting up email delivery

Now that you have a document template loaded into Formstack Documents, you need to set up the delivery so your purchase orders will be emailed to the appropriate supplier once they are generated. A Delivery is a Formstack object which sends out a document. To set up your email delivery, click + New Delivery while on the Deliver tab and then select Email. This feature should feel familiar as it is very similar to Quickbase email notifications. The PDF will be sent as an email attachment. You can customize the subject and body of your email. Also, you can include dynamic data from your form. You simply use the same merge field formatting here as you did in your document template.

Note: Any field you want to include in your email must be included in your document as well.

 

Overview of example pipeline

The document template has now been set up. It’s been designed and uploaded to Formstack Documents, with automatic email delivery ready to go. Now it’s time to build the pipeline which will generate the purchase orders.

Before designing the pipeline in Quickbase, the auto manufacturer's procurement team defined in detail how the automated process should work. The overall goal of the pipeline is to build the PDF version of the form in Formstack Documents when a purchase order in Quickbase enters the Ready status.

After considering how the data is stored for the purchase orders and line items, the procurement team planned these three steps:

  • Step A: Trigger the pipeline when a purchase order in Quickbase is updated, where its Order Status becomes “Ready”.
  • Step B: Search the Line Items table for all line items that are part of the purchase order.
  • Step C: Take supplier details from the purchase order, and the item / quantity / price from each line item, and use it to generate a purchase order PDF.


NOTE: This method can be used with up to 100 line items.

 

Pipeline Step A: Trigger when purchase order is ready

The goal of Step A is to trigger the pipeline when a purchase order in Quickbase enters the Ready status.


Here’s how to set it up:

  1. Create a new pipeline and give it a name.
  2. Open the Quickbase channel on the right.
  3. Connect to your Quickbase account if you haven’t already done so (click here to learn how).
  4. Click Records, then drag the Record Updated trigger onto the canvas as Step A.
  5. Under Table, select Purchase Orders.
  6. Under Trigger on Any of These Fields, select Order Status.
  7. Under Specify Fields for Use in Subsequent Steps, select the Order Status field since we’ll be using it for the trigger condition in a minute. Then, select each field that you want to appear on your purchase order (Payment Term, Supplier Email, Grand Total, etc).
  8. Click Add Conditions.
  9. Build a condition that reads like this: (Order Status) (is) (Ready).

 

 

Pipeline Step B: Search for all line items within the purchase order

The goal of Step B is to search the Line Items table for all records related to the purchase order. This allows us to include all line items in our PDF.

Here’s how to set it up:

  1. From the Quickbase channel on the right, drag the Search Records query onto the canvas as Step B.
  2. Under Account, select the appropriate user token.
  3. Under Table, select Line Items.
  4. Under Fields, select Related Purchase Order, Item Number, Description, Unit Price, Quantity, and Line Item Total.
  5. Click Add Conditions.
  6. Start building the condition so the first line reads: (Related Purchase Order) (equals)
  7. From the box in the middle of this page, drag Record ID from the purchase order in the bottom line of the condition.



  8. Quickbase automatically adds a FOR EACH loop after query steps, but in this case we do not need it. Click the trash can icon next to FOR EACH to remove the loop.



Pipeline Step C: Generate purchase order PDF
The goal of Step C is to take the info from the Purchase Order table and from the Line Items  table, and combine them to generate the purchase order PDF in Formstack Documents.

Here’s how to set it up:

  1. Open the Formstack Documents channel on the right.
  2. Connect to your Formstack Documents account if you have not already done so (click here to learn how).
  3. Drag the Document Merge action as Step C.
  4. Under Account, select your Formstack Documents account.
  5. Under Merge URL, select the document template you want to use. In this case, we’ll use Purchase Order.
  6. After selecting the document template, a series of boxes appears below, with one box for each merge field you’ve included in your document template.
  7. Drag fields from the box in the middle of the page into their corresponding boxes on the left.

 

  1. Under Custom JSON, we’ll pass Formstack Documents the list of line items associated with the purchase order. You can do this by typing the following jinja expression in the box:

    {"items": {{b|to_json}}}

 

Testing your pipeline

To test your pipeline, first click the toggle next to Pipeline off to turn it on.  Next, switch to your Quickbase app, and set up a test purchase order with line items to make sure the pipeline is working properly. The activity log in Pipelines will show you the progress of the pipeline, and give you details about each step the pipeline is taking.

 

Taking it to the next level

After automating this process, how else could the Formstack Documents channel help this hypothetical auto manufacturing company be even more efficient? Here are a few ideas to inspire you:

  • Once the purchase order is emailed to the supplier, you could save a copy of the PDF in Quickbase. You can do this by including the Quickbase Record ID# in your PDF, and then setting up a Quickbase Delivery within Formstack Documents. (found under the Deliver tab)
  • If the document is something that needs to be signed, you could create another pipeline that integrates with DocuSign and send the document that way instead.



Further reading

 


Learn more about Pipelines

No RepliesBe the first to reply