Modify a database

Owned by Former user (Deleted)
Last updated: Apr 23, 2024 by Denise McGettigan
10 min read

Whether the Phocas Implementations team designs your databases or you do it yourself, as time goes on, you might want to modify those designs to meet your business’s needs. It's not unusual to go back and make changes, either to add new elements, delete things that are no longer needed or simply make some changes to the name or order of the items.

In Designer, you can take the following actions:

1 Rename an item | 2 Reorder items | 3 Change the data type of an item | 4 Group properties | 5 Customize dimensions (hide the code, create groups, and include a group as a property) | 6 Customize measures (change the format or create your own calculated measures) | 7 Customize period types (set the period types, and configure the moving period count and number of days to summarize) | 8 Set a stream as Balance Brought Forward | 9 Modify the general database options (customize activity filter labels, suppress empty dimensions and streams, and set the Retained Earnings account code)

Some changes you make in Designer can potentially affect dashboards, favorites, and other items in Phocas that are connected to your database. For example, if you delete the Sales Rep dimension in Designer, this will cause a user's Sales Rep performance pie chart favorite to fail. Therefore, you should take particular care when deleting dimensions, properties, measures, streams, and dates. Renaming items is fine. For example, if you rename the Sales Rep dimension, the user's Sales Rep performance pie chart favorite will still work.

After you modify a database, you need to save and rebuild the database to apply your changes.

Rename an item

Rename dimensions, properties, measures, and streams, to give them more meaningful titles. Be careful not to use any of the reserved words and characters.

Click the Edit button next to the item name, then enter a new title (name) and click Save.

In measures, if you are designing a source database for use in Rebates (the database that Rebates will interrogate), some characters must not be used in Measure names, as this can adversely affect rebate calculations. Measure names must only contain the following characters: "_abcdefghijklmnopqrstuvwxyz". Spaces cannot be used.

Reorder items

Change the order in which the dimensions, properties, measures, and streams display in their respective menus in Phocas. By default, items display in the order in which you map them.

Watch the Design your own database video from 2:13 to 2:17 for a demonstration.

Click the item’s Move button, then drag and drop it in the new position.

For example, in the image below, the Budget stream is being dragged from next to Sales across to the right of Orders. This sets it as the third stream in the list in the user's menu.

The order in which properties are mapped, and their naming convention, affects the results that users see when using the Map charts (Marker, Circle, and Heat) feature in Analytics. 

  • Country AUS does not map. Use AU or Australia.

  • Use Due date not Date due.

To test mappable properties enter the string into https://www.openstreetmap.org.

Change the data type of an item

Change the data type of dimensions, properties, measures, streams, and date (in some circumstances) sections. Changing the data type is particularly helpful for classifying your properties, as the data type can make a difference to the way the data can be analyzed and sorted in Phocas.

Click the Edit button next to the item name, select the required data type from the Type dropdown list and click Save.

The available data types depend on the component:

  • Text - The default data type for dimensions and properties.

  • Number - The default data type for measures.

  • Boolean - Available for properties only. See below for more information about Boolean property types.

  • Date - See Map the date for more information.

  • Paragraph - Available for properties only. Useful if you have a property containing a lot of text, such as a product description, and you want users to be able to view and export it in full. In the Phocas grid, the text is truncated to 100 characters, but users can click items to view the full text in a separate window.

These are not case-sensitive.

Data

Result

1

True

'active'

True

'yes'

True

'y'

True

'true'

True

0

False

'inactive'

False

'no'

False

'n'

False

'false'

False

Group properties

Put properties into groups by prefixing the property name with the group name, a dash, and a right arrow (without spaces), like this: Name->

Grouping is handy when you have a large list of properties. For example, suppose you want to group the properties Street, Country, and Postcode into a group named Address. After you add the prefix Address-> to the name of each property, users will see these properties grouped in the Property menu in Phocas.

Click the Edit button next to the property name, then enter the group name prefix and click Save.

Customize dimensions (hide the code, create groups, and include a group as a property)

Set matrix mode column headings (known as captions in the options menu on the grid) to default to name only, so users only see the name for the selected dimension. As the code caption is unavailable, users don’t have the option of changing it, for example, to name and code or just code.

In the Dimensions section, click the Edit button next to the dimension name, then select the Hide Code checkbox and click Save.

The following information is for adding groups in Designer. You can also add and manage dimension groups in your Financial Statements module.

Put your dimensions into groups for analysis purposes. A group can be anything that works for your business, as long as the data exists. For example, you might want to:

  • Group customers into regions or types, or even by which sales rep looks after them.

  • Group products by category and sub category.  

  1. In the Dimensions section, click the mapped dimension. The Design tab changes from the default Stream mode into Dimension Mode, where you can arrange your dimensions into groups.

  2. Drag the relevant raw data file from the data sources panel into the Drag here box.

  3. Map the data file:

    1. A data box displays to the left of the raw data with a red heading. This represents the data you've already mapped. You need to link your raw data to it. Locate the column that has the matching data and drag it across. The column heading will turn green.

    2. To create a dimension group, drag the raw data column on top of the appropriate dimension. For example, drag the Minor Section data file on top of the Product Dimension. It will then appear below, grouped.

  4. Reorder the dimensions in the group, if required:

    1. Select the relevant dimension. The data files display from left to right across the screen and this determines the order in which they are processed during a database build.

    2. Drag the data files to change their position.

  5. Exit Dimension Mode and return to Stream mode: either click the dimension again or click on any blank space in the dimension box.  

Customize measures (change the format or create your own calculated measures)

Customize period types (set the period types, and configure the moving period count and number of days to summarize)

Set a stream as Balance Brought Forward

Set a stream as Balance Brought Forward to give the transactions an opening balance plus movements. This is typical in a Financial database, where the value in a General Ledger account for a selected month is effectively the sum of all prior transactions. It is also useful with Inventory Movements databases, which also typically have an opening stock 'as set by a stocktake' and adjustments in and out for receipts and dispatches.

Click the Edit button next to the stream name, then select the Balance Brought Forward checkbox and click Save.

Modify the general database options (customize activity filter labels, suppress empty dimensions and streams, and set the Retained Earnings account code)

The Options button on the top right of the Design screen (next to the Save button) gives you access to general database options.

Customize activity filter labels

Customize the activity filter labels that appear in the Phocas grid. For example, in a Sales database, you might prefer the labels Sales and No Sales to Activity and No Activity, or in a Purchasing database you might want Purchases, No Purchases, and so on.

Enter your synonyms into the respective boxes, and click Save.

Suppress empty dimensions and streams

Remove dimensions and streams from the front-end view of the database when they do not contain any data. The items remain in the database design but don't display for users when they open the database.

Select the Suppress empty dimensions and streams checkbox and click Save.

Set the Retained Earnings account code (financial databases only)

Set the Retained Earnings account code you want to use to calculate Retained Earnings for the Balance Sheet. When the database is being built, Designer uses the specified code to assign Retained Earnings to the correct place. If no code is specified, it creates its own code, called Retained Earnings. Only accounts within the Equity category will work.

Enter a value in the Retained Earnings Code box, and click Save.