# Modify a database {% hint style="info" %} User permissions: Administration > Databases and Sync You also need to be the [owner of the database](https://docs.phocassoftware.com/administration/databases-administration#change-the-name-and-owner-and-other-general-settings). {% endhint %} 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. This page outlines how to customize the database components. Other changes you can make to a database design are covered on other pages: [Add (connect to) more data files](https://docs.phocassoftware.com/administration/designer/design-a-database/add-connect-data-to-a-database), [replace existing data files](https://docs.phocassoftware.com/administration/design-a-database/add-connect-data-to-a-database#add-connect-datatoadatabase-replaceanitem-useexistingmappings), and [unmap or delete items](https://docs.phocassoftware.com/administration/design-a-database/map-data-to-the-database#mapdatatothedatabase-unmapordeletemappeditems). {% embed url="" %} Design your own database {% endembed %} {% hint style="warning" %} * After you modify a database, you need to save and rebuild the database to apply your changes. * 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. {% endhint %} ## Open a database design Use one of these methods to access a database design: * On the **Databases** page, select the database and click the **Design** button.

* On the **Databases** page, click the blue link to open the database maintenance page, then click the **Design** button.

* [Open the database](https://app.gitbook.com/s/7pj8v25BOyqZTdG5mdD1/getting-started/databases) then click the blue arrow next to the database name and select **Design database**.

## 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](https://docs.phocassoftware.com/administration/reserved-words-and-characters). Click the **Edit** button next to the item name, then enter a new title (name) and click **Save**.

Note about measure names and Rebates

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. {% hint style="success" %} Watch the *Design your own database* at the top of this page from 2:13 to 2:17. {% endhint %} 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.

Note about Map charts

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](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.

Note about Boolean property type

These are not case-sensitive. ![](https://content.gitbook.com/content/vQCmtGTR7MsjcI62Vdki/blobs/7XMvGYwNk8IdxUYYoaei/image.png)

## 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 You can hide the code, create groups, and include a group as a property. ### Hide the code in dimensions Set [matrix mode](https://app.gitbook.com/s/itAvDF3MljQXV0rjHRP7/compare-data-with-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**. This feature doesn’t apply when you add levels to the grid (grid nesting). The code is *not* hidden when you view the nested grid. The Code property always displays in the nested grid because it’s required to link the nested rows together correctly. ### Group dimensions The following information is for adding groups in Designer. You can also [add and manage dimension groups](https://app.gitbook.com/s/KhoFIsurMPEjkuBz9YkN/managing-statements/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. {% hint style="success" %} Watch the *Design your own database* video at the top of this page from 2:22 to 4:10. {% endhint %} 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: * 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. * 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. Exit Dimension Mode and return to Stream mode: either click the dimension again or click on any blank space in the dimension box. ### Include a dimension group as a property Set a dimension as a property. This *dimension* property is different from and independent of the *transaction* properties that you [map to the database](https://docs.phocassoftware.com/administration/designer/design-a-database/map-data-to-the-database), so they do not display in the regular **Properties** section in Designer. After mapping a dimension group (see section above), click the **Edit** button next to the dimension name, then select the required property from the **Include as Property** dropdown list and click **Save**. The newly created property will be named as per the dimension title.

## Customize measures You can change the format or create your own calculated measures. ### Change the format of measures Determine the default format of a measure that users see when they open the database. Users can overwrite this by customizing the format of their own measures. In the **Measures** section, click the **Edit** button next to the measure name. Then, in the **Measure** window, either select a predefined format from the dropdown list or select the custom option and enter your own format.

Learn about custom measure formatting

You can manually enter the required format into the box. For example: * To remove thousands, enter: #,##0,.00 * To remove thousands and use K as a descriptor, enter: 0,0,.00,"K" * To remove millions, enter: #,##0,,.00 * To display negative figures in parentheses, enter: #,0.00;(#,0.00);0.00 To specify a prefix, suffix, and/or number decimal places, or turn the thousands separator on or off, click the **More options** button next to the box.

Use the arrows to move to the settings for the next measure, if required, then click **Save** when you're finished.

### Create a calculated measure Often, raw data contains only basic measures, such as cost, value, and so on. You can use these measures as the basis of a new *calculated* measure, such as profit (value minus cost). {% hint style="success" %} Watch the *Design your own database* video at the top of this page from 4:08 to 4:20. {% endhint %} 1. In the **Measures** section, click the green plus button. 2. Enter a title (name) for the new measure.\ Note: 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. 3. Enter a mathematical calculation using your existing measures. You can use the usual notation (+,-,\*,/), and any of the other non-calculated measures in your expression. 4. Set the data formatting, if required. 5. Click **Save**.

## Customize period types You can set the period types and configure the moving period count. ### Set the period types for a database When you create a new database, the default [period types](https://docs.phocassoftware.com/administration/periods-administration/period-types) (Year, Month and Day) are automatically selected for you. You might want to remove one of these period types or add others. Over time, as your needs change, you can review this list and change the period types that are available to users. If you create a new period type, for the new period type to be available to users, you need to add it to the database, then rebuild the database. 1. In the **Date** section, click the **Edit** button at the top. 2. In the **Period Types** window, select the checkboxes of the period types you want to make available in the database. Clear the checkboxes of any period types you do not want. 3. Configure the moving period count for each period type if required (see next section below). 4. Click **Save**.

### Configure the moving period count for each period type Set how many moments constitute a moving period (as used in [Use Moving mode](https://app.gitbook.com/s/itAvDF3MljQXV0rjHRP7/customize-your-view-analytics/change-the-mode/moving-mode)). On a Month period type, this is typically 12 (the default), which will ultimately display what many refer to as a *moving annual total*. However, you can select any value. For example, if you select 3, it makes the moving mode display rolling quarters instead of years. For period types other than month, 0 moving periods is the default. Note that in Moving mode, 0 does not mean *none*. It is more like a starting point, as Moving mode takes the number of moments in your active period, counts back that many *moving* moments, and then adds those to each column. For example, a setting of 0 when using a period of the *Last 7 days* displays rolling 7 days but switching to a period of the *Last 30 days* displays rolling 30 days. 1. In the **Date** section, click the **Edit** button at the top. 2. In the **Period Types** window, select the period type and click the **More** button. 3. Use the arrows to set the moving period count and click **Save**.

## 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**. ## Add shortcut links You can add shortcut internal links that allow users to move from the grid directly to a dashboard, favorite or different database\*. For example, click on a Sales Rep and open that particular Rep Scorecard dashboard. You can also add links to an external application. For example, click on a customer to open that customer's account in your ERP system. In the example below, the **Code** property displays on the user's screen as a hyperlink in blue text. If the dimension has a single link, clicking the code value will open the link. If the dimension has two or more links, as in this example, clicking the code shows a dropdown menu with all available links.

{% hint style="info" %} \*You can't link to financial databases (those that open in the Financial Statements module). {% endhint %} ### Link to a dimension Click the **Link** button next to the relevant dimension. In the Links window, click the green plus button to add one or more new links, then enter the following details: * **Name**: The name that displays in the grid. * **URL**: The link code. See below for more information. Note there is no validation of the link at this stage.

Syntax for linking to databases, dashboards and favorites

Internal links can be set up using a syntax starting with '*database*', '*favourite*' (spelt with a 'u') or '*dashboard*', as outlined below, followed by name (or ID) and the required query. **Link to another database**\ database:Name|query\ For example, database:GL|Dimension=Customer\&Mode=Variance\&Customer=%% **Link to a dashboard**\ dashboard:Name|query\ For example, dashboard:Customer Score Card|Customer=%% **Link to a favorite**\ favourite:Name|query\ For example, favourite:Declining Products|Customer=%% **Points to note**: * If using ID instead of Name, the ID is an identifier displayed as the last part of the URL. * Query is typically the name of the dimension you want to filter on, followed by '=%%'. And, if required, the property relating to the dimension. * The syntax you enter must use the UK English spelling, which is **favourite** with a 'u', even if Phocas displays the word spelt in the usual way everywhere else in the product. * On the database link, the mode defaults to Period, so it doesn't need to be specified. * The names of the databases, favorites and dashboards are case sensitive. If a link doesn't become active, check the spelling.

Click **Save**.

### Link to Rebates If you have Phocas Rebates, you can link to an individual rebate, so users can open the rebate directly from Phocas. Use the URL */Rebates/Rebate/Index/%%*

### Link to a transaction property Add links to transaction properties so that in [Transaction mode](https://app.gitbook.com/s/itAvDF3MljQXV0rjHRP7/customize-your-view-analytics/change-the-mode/transaction-mode), users can click on a property (such as *Customer Website*, *Invoice* or even *Invoice line*) to open an external application at a specific place. For example, clicking on an *Invoice line* transaction property would open that particular line in your accounting system. These links are only visible to the user when in Transaction mode. Click the **Link** button next to the relevant property. In the Links window, click the green plus button to add one or more new links, then enter the following details: * **Name**: The name that displays in the grid. * **URL**: The link code. Enter the following syntax (placeholders)

Syntax

**Link to a property**\ %%property\_name%% \ For example, Value=%%invoice\_number%%\&AnotherValue=%%duedate%% **Link to other dimension properties within a transaction property link**\ %%dimension|dimension\_name|property\_name%% (where the word ‘dimension’ is static).\ For example, %%dimension|Ship To|Name%%

Click **Save**.

After you rebuild the database, users will see a link similar to those below, which, in this example, would take them to their accounting system.

### Add an external link As well as linking to other Phocas databases, this feature can be used to open external applications and webpages using the same parameter passing method. This is particularly useful when the partner ERP system can be opened using URLs. The syntax is the same, except for the domain part: \ \&#xNAN;** Replace the %% placeholder with the customer code of the selected customer. To pass a property instead of the code, use the syntax %%PropertyName%% ### Link to a system setting (advanced administrators only) Administrators with permissions to view system settings can enter a system setting, for example, to reference a setting that is actually a URL. To do this, enter the following syntax in the link dialog. %%setting|setting\_name%% For example, %%setting|DynamicsURL%% If the setting doesn’t exist then blank is returned. You can also chain many together, such as, %%setting|DynamicsURL%%%%setting|DynamicsSiteName%% ## Modify the general database options The **Options** button on the top right of the Design screen (next to the **Save** button) gives you access to these general database options: customize activity filter labels, and suppress empty dimensions and streams.

### Customize activity filter labels Customize the [activity filter](https://app.gitbook.com/s/itAvDF3MljQXV0rjHRP7/customize-your-view-analytics/use-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**.