Flow idea #2: Maintaining Deliverable List using Flow

This example flow demonstrates how to create ProjectWise document with a document code. It also shows how to use Excel spreadsheet for additional data and how to handle error situations without stopping flow execution.

Note that this flow example is a suggestion for ideas how to use Flow connector. It is not meant to provide the ultimate solution deliverable management using Bentley tools.

Scenario

Let’s imagine here is an Excel spreadsheet that serves as a deliverable registry. It is also used to track deliverable progress as percent complete.

Document controllers need to create deliverable documents without files in a project when new deliverables are identified, or a project is initially seeded. Also, they need to update percent complete in the spreadsheet every day. It is a time-consuming activity, so an automation would help!

For our example the spreadsheet looks like this:

The ID column is used for identification of rows by Excel Flow connector. In this eample ID column has a formula =row() to ensure that each row has a unique value in it.

The flow

The flow runs automatically every day at 10 am. Document controller used built in Flow recurrence trigger. See Variations chapter about other options to trigger this flow.

Then flow reads the spreadsheet stored in Microsoft Teams using Excel Online (Business) connector.

 

The next part of the flow is a loop through the returned rows to process each. (Flow adds Apply to each automatically when action references a property from a list of items).

Processing each row

The Status column in spreadsheet defines what action will be taken:

  • If column value is ‘New’ then flow will create a new document, record document name to spreadsheet and change Status to ‘Created’.
  • If column value is ‘Created’ then flow will update Percent Complete column form document attribute.

Inside the loop there is a Switch statement that controls the flow:

 

Create a new document

The first Case is for creating a new document when Status is ‘New’:

 

Document controller wants to generate document number for the document and then use it as name of the document. This is achieved in two steps: first a document is created with a temporary name in the discipline folder:

 

The document number is generated by ProjectWise when all attributes making document number are specified. The attributes values are taken from deliverable spreadsheet:

 

Note: to set serial number, you will need to remove read only flag in Complex environment via ProjectWise Administrator or else it will not be displayed in Flow. Note this change will not make it editable in ProjectWise interface because it is part of document code.

Once document is created, the Document Number will be known. Now the second step will rename the newly created document using the document number.

Document GUID from creation step is used to identify the new document.

Note that actions to create or modify document return attributes as they are in datasource after the action was completed. So, for example, if some attributes have default values then they will be returned after creation even if inputs were empty.

The last step in creation case is to update spreadsheet. Not the use of ID column for row identification. See the beginning of this article for how unique ID is ensured.

 

Updating Percent Complete

The second case will be executed if document already exists. In this case it will use Find document by Path or GUID action to retrieve document attributes and write percent complete value into spreadsheet:

Handling Errors

The last step in this Flow demonstrates a simple error handling technique. It may happen so that spreadsheet contains information that is out of sync with ProjectWise datasource. For example, existing deliverable document may be renamed, or new document attribute combination may produce a combination that was already used before. Usually when any action in flow fails the whole flow will fail. In this case Document Controller wants Flow to record failure and continue processing other rows.

To record the processing error update row action like this was added after switch statement:

Then it was configured to run only if an error occurs within Switch by setting ‘Configure run after’ options:

Then:

Variations

This flow is triggered on schedule. Here are other options that could be used to run a flow. Since this flow does not require inputs, essentially any trigger that could correlate to changes in deliverable list could be used.

Here are some trigger examples:

Deliverable Spreadsheet can have more columns and more actions not only creating new or reading percent complete.

The flow can be re-purposed for other situations when a spreadsheet could be used to maintain a set of documents in a convenient excel interface.

 

Anonymous