How to Prevent a Purchase Requisition from Being Converted to a Purchase Order More Than Once

If your workflow uses the "Purchase Requisition" sheet, the "Purchase Order" sheet, and a Convert Records Action Button to transfer requisition data into a purchase order, you may accidentally click the button more than once or forget you already did, causing the same requisition to be converted multiple times.

The Convert Records button can quickly transfer data between sheets, but it does not provide a way to limit the Convert Records Action Button, since in some cases multiple conversions are required, for example, when an order is shipped in batches. The system also does not create a field to record conversion status, so there is no direct reference to check whether the action has already been executed.

To prevent a record from being converted more than once, you can use one of the following methods:

Take the "Purchase Requisition" to "Purchase Order" conversion in the "Purchasing" template as an example. It has a Convert Records Action Button as shown below:

1. Set a Unique Value Field as Unique

Convert a Unique Value field from the source sheet to the target sheet (the field must be an independent field), and set this field as Unique.

Step 1. Find the Unique Key Field from the source sheet

You'll need to find the Unique Value field from the listed fields in the Action Button settings. If you don't have such a field, consider updating your Action Button to include one. In the screenshot above, the Action Button's source sheet is the "Purchase Requisition" sheet. Furthermore, the Unique Value field is the "PR #" field.

Step 2. Set the "Unique" attribute to the corresponding field on the destination sheet

In the screenshot above, the corresponding field of the "PR#" field on the destination sheet is the "From PR #" field. Hence, please apply the Unique attribute to it.

When users click the Convert Records Action Button more than once, the system will reject the request and display an error message indicating that the "Purchase Order" already exists, resulting in a failed conversion.

2. Lock or Hide the Action Button Using Conditional Formatting

Add a field to record whether the conversion has been executed. Then, create an Update Values button to update this status field and combine it with the original Convert Record button. Finally, use Conditional Formatting to lock or hide the Convert Record button when the status field is marked as executed.

Example: Converting a Purchase Requisition to a Purchase Order (Using the Create PO Button)

Step 1: Add a Conversion Status Field

Add a Checkbox field to the "Purchase Requisition" sheet and name it "Converted?", with the Default Value set to "No".

Step 2: Create an Update Values Button

Create an Update Values button that updates the "Converted?" field to "Yes".

Next, combine the "Converted?" button with the "Create PO" button and name the combined button "Create PO + Confirm Conversion". When this combined button is executed, it will create the "Purchase Order" and simultaneously set "Converted?" to "Yes".

Step 3: Lock or Hide the Action Button with Conditional Formatting

In Conditional Formatting, set the rule so that when "Converted?" is "Yes", the "Create PO + Confirm Conversion" button is locked or hidden to prevent duplicate conversions. You can also customize the warning message that appears when a locked button is clicked.

After setting this up, it is recommended to hide any original buttons that are no longer needed and set the "Converted?" field as Hidden or Read Only to prevent manual changes.

If conversion data contains errors and you need to redo the conversion after deleting incorrect records, you can create another button (as in Step 2) to reset "Converted?" to "No", which will unlock the previously locked button.

The logic for preventing a record from being converted more than once also applies when converting a "Purchase Order" to a "Delivery Order".