Maintain the Set Price Excel File

About the Set Price Excel File

The Set Price Excel File will have the following columns by default:

(Click the image to enlarge it).

Column	Description
DrugCode	Fred Drug Code
DrugDescription	Drug brand name, form, and strength
BrandName	Drug brand name
Form	Drug form
Strength	Drug strength
PackSize	Drug pack size
GenericName	Drug generic name
GenericCode	Fred Generic Drug Code
ManufacturerCode	Drug manufacturer code
GTIN	Barcode (Note: Only the first barcode is included)
HQID	AppCAT HQID (coming soon)
AverageWholesalerCost	Drug average wholesaler cost
CreatedDate	Date drug became available in Fred Dispense Plus
Price	User specified set price. Accepts values $0.00 or greater, up to 2 decimal places. Cannot be empty or include a dollar symbol.
Quantity	User specified quantity. Accepts only integers, value must be 1 or greater. Cannot be empty. For a multipack or broken pack quantity row to be accepted, there must be a row for the base (single pack) quantity for that PriceType and Group
PriceType	User specified price type. Accepted values - All, Private, S3, PBS. Entire column can be empty, this will be treated the same as having a value of All
Group	User specified pricing group e.g. Level1, Level2 etc. Values are not case sensitive (e.g. Tier1 will be treated the same as tier1). Can be left empty if you don’t have multiple price groups.
RemovePrices	User specified. Insert TRUE to trigger the removal of set prices for the drug, otherwise leave empty.

It is recommended,but not essential, that the columns are in the same order as above,

The Set Price Excel File must have the DrugCode, Price, Quantity, PriceType, Group and RemovePrices columns as they are used by the import process. The name for these columns must be as they appear in the table above. The other columns can be hidden or removed.

Excel .xlsx files are supported and it is also important to note that only the first worksheet within the .xlsx file is processed.

A template with all currently active drugs can be provided as a starting point. However, the Set Price Excel File needs to be maintained by the user on an ongoing basis.

Some general considerations when maintaining the Set Price Excel file:

• The pricing in the Excel file will replace any existing set price in Fred Dispense Plus when it is imported.

• The set price for any drugs that are not part of the import process will remain in its current state.

Configuring set price – PriceType column

The PriceType column can be configured such that set prices can configured based on price type.

Example: Setting only PBS Under-Co Price

I want a PBS Under-Co Price of $10.00

(Click the image to enlarge it).

Example: Setting only Private and PBS Under-Co Prices

I want a Private Price of $7.00 and PBS Under-Co Price of $10.00

(Click the image to enlarge it).

Example: Setting only prices for all price types

I want a Private Price of $7.00, Schedule 3R Price of $8.00 and PBS Under-Co Price of $10.00

(Click the image to enlarge it).

I want all price types to be $9.00 for a drug

Each of the options below will achieve the same result.

(Click the image to enlarge it).

Configuring set price – Quantity column

The Quantity column can be configured to create set price for single pack quantities, multipack quantities and broken pack quantities (where applicable).

For a multipack or broken pack quantity row to be accepted, there must be a row for the base (single pack) quantity for that PriceType and Group

Example: Setting a Private Price for multipack quantity

For a drug where pack size is 30, I want a Private Price of $12.99 for a quantity of 60, and $15.00 for a quantity of 90. I want the Private Price for 1 pack (i.e. 30) to be $10.00.

(Click the image to enlarge it).

Example: Setting a Private Price for broken pack quantity

I want a Private Price of $4.00 for a quantity of 15, when then full pack quantity is 30. I want the Private Price of $10.00 for the full pack quantity.

(Click the image to enlarge it).

Configuring set price – Group column

The Group column can be configured to create set price for the same drug with different price groups. This is especially useful for scenarios where there are multiples stores using the same Set Price Excel File – only one file needs to be maintained.

See Import a Set Price Excel File for further details on how to select the pricing groups and their priority during the import process.

 

Example: Setting prices for two different sets of drugs for two different price groups

For SOME stores (Group1 stores), I would like Drug A, B and C to have certain prices.

For OTHER stores (Group2 stores), I would like to have DIFFERENT prices for the same set of drugs.

(Click the image to enlarge it).

For SOME stores (Group1 stores), I would like Drug A, B and C to have set prices.

For OTHER stores (Group2 stores), I would like Drug A, B, and D to have set prices.

The set prices for Drug A and B are the same across all stores.

Each of the options below can achieve the same result when the correct Pricing Groups are selected prioritised correctly.

(Click the image to enlarge it).

Example: Setting prices for two different classes of drugs with different price groups

For Blood Pressure medications, I would like two groups of pricing available for stores to select.

For Cholesterol medications, I would like two groups of pricing available for stores to select.

Stores can select the desired combination of pricing groups for each class of medications – e.g. Import the prices for blood pressure medications using the prices from BloodPressure1 pricing group and the prices for cholesterol medications using the prices from Cholesterol2 pricing group.

Configuring set price – RemovePrices column

The RemovePrices column is used in scenarios where set prices are to be removed from a drug. The removal of set prices will apply based on the value in the Group column.

Example: Removing ALL set prices from a drug including multipack prices

There are currently prices set for a drug, include multipack pricing. After importing the Set Price Excel File, all set prices need to be removed from the drug.

 

Add ‘True’ to the RemovePrices column to all the rows for that drug.

(Click the image to enlarge it).

Alternatively, importing a file containing a single row, like the example below, will also remove all set prices.

(Click the image to enlarge it).

Example: Removing some set prices from a drug

To only remove some set prices, add ‘True’ to the row which reflects the set price you want to remove.

Only remove the PBS Under-Co Price, whilst keeping Private and Schedule 3R Prices

Only add ‘True’ to the RemovePrices column for the rows where PriceType = PBS

(Click the image to enlarge it).

Alternatively, the same can be achieved without utilising the RemovePrices column. The below example will also remove the PBS Under-Co set price whilst retaining the set prices for Private and Schedule 3R.

(Click the image to enlarge it).

Take care when using the RemovePrices column to remove set prices for multipack pricing. For multipack pricing to work, there must be a set price for the default pack size quantity.