Data Tools

The chapters below describe the feature set of Data Tools page.

1. Action Worksheet

2. Record Permission Wizard

Action Worksheet

You can perform actions in your own database, like filling fields, but you can also include them in a project so you can transport these actions to the customer database and execute them in there.

You can find the Action Worksheet in menu Data Tools or access it from the project- or transport card.

Table of Content:

1. Setup

2. Copy Data

3. Delete Data

4. Transfer Data

5. Fill Fields

6. Run Report, Codeunit

7. Rename Data

8. Renumber Object

9. Renumber Field

10. Execute SQL Query

11. Example SQL Query to Change Data

12. Example SQL Query to Add a View

13. Execute DOS Command

14. Execute PowerShell Command

15. Save Options

Action Types

Setup

When you are using a customer license and you want to update data that is only allowed through indirect permissions you can enable the setting Use Indirect Permissions when executing actions.

Now it possible to modify data in tables like G/L Entry.

Copy Data

You can copy data in the same or another table or between two companies.

Example
Copy the Customer Address to Address 2.

1. Select the fields that you want to copy.

2. Select the table where you want to copy the data to. In our case the same Customer table.

3. Click the assist-edit button of Into Fields to map the fields where you want to put the data in.

4. Press Start to get the result.

Delete Data

With this action type you can delete data from all records, filtered records and from specific fields. So if you have to delete a field from a table you can first empty it with this action type.

Transfer Data

Transfer data from one table to another table in another database.
Example

1. Action Type Transfer Data

2. Fill in the Table No. were you want to transfer data from.

3. Press Export

4. The Object Manager Exports the action + data as a FAB file

5. Save the file

6. Open the Customer database

7. Open the Action Worksheet

8. Press Import and Open the FAB file

9. The Action is imported in the Action Import Worksheet

10. Press Start

11. Data from the development database is now in your Customer database

Fill Fields

Suppose you want to fill the Customer Price Group in the Customer table with value “PG001”.

1. Open Action Worksheet

2. Action Type Fill fields, “Object No.” 18 (Customer)

3. If you use multiple companies in NAV you can select All or a specific Company to perform the action on

4. Push the assist-edit button in field Fields

5. Select the “Customer Price Group” field by checking the Selected field

6. Fill in the field Value “PG001”, this is the value to fill

7. Return to the Worksheet and push Start. For all the Customers the Customer Price Group is filled with value “PG001”

Run Report, Codeunit

You can execute reports end codeunits.

Rename Data

With the rename data option you can rename data.

Renumber Object

The renumber object function can be used to give an object another number. All references to this object will not be changed. So if you are doing a renumber action in your development database it is preferred to do this with the renumber objects function. For more information see chapter Renumber Objects. If this renumber action is also needed in your customer database then it is possible to copy this renumber action to a specific project as Action Before with the function Copy to Project as Action Before.

*Note
Make the Actions Before before you start the renumbering.

Renumber Field

The renumber field function can be used to give a table field another number. All references to this object will not be changed. So if you are doing a renumber action in your development database it is preferred to do this with the renumber fields function. For more information see chapter Renumber Fields. If this renumber action is also needed in your customer database then it is possible to copy this renumber action to a specific project as Actions Before with the function Copy to Project as Action Before.

*Note
Make the Actions Before before you start the renumbering.

Execute SQL Query

You can execute a SQL query Press Edit SQL Query and make your query in your text editor.

When finished save the SQLQuery.txt file and press OK in the pending dialog.

*Note
The string COMPANYNAME will be replaced by the companies you have selected in the action.

Example SQL Query to Change Data

If you e.g. want to move the content of the address field to the address 2 field in the customer table you can use the following query.

UPDATE [COMPANYNAME$Customer] SET [Address 2] = [Address] WHERE [Address] <> ''; UPDATE [COMPANYNAME$Customer] SET [Address] = '';

Example SQL Query to Add a View

If you want to send a LinkedObject to your customer database you also want the corresponding view or table to be created in your customer SQL database.

You can do this by creating two actions of type “Execute SQL Query”. The first one is to remove the existing view.

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[COMPANYNAME$No_ of Customers per Location]')) DROP VIEW [dbo].[COMPANYNAME$No_ of Customers per Location]

The second is to add the new view.

CREATE VIEW [dbo].[COMPANYNAME$No_ of Customers per Location] AS SELECT [Location Code], COUNT(No_) AS [No_ of Customers] FROM dbo.[COMPANYNAME$Customer] GROUP BY [Location Code]

Execute DOS Command

You can execute a DOS Command as action. This can be useful if you need to start an application or script during or after an import. This can also be used to restart a NAS.

Execute PowerShell Command

You can execute a PowerShell script as action. This can be useful if you e.g need to import a license file and restart the service.

Save Options

Record Permission Wizard

The “Record Permission Wizard” creates or modifies roles. You can add these permissions to a project and/or save them to your database.

1. Select Data Tools > Record Permission Wizard to start the wizard
   You can also start the wizard from the Project Card

2. Create the Role Name or select an existing role

3. Describe the role

4. What do you want to do? You have 3 options:

   • Create new permission set

   • Modify existing permission set

   • New permission set based on existing

5. Press Next

6. Now the recording starts; you perform now every action on the object(s) the user is allowed for the role you created.

7. When ready recording press Next in the Wizard

8. Press Next to end the wizard and get the new role created or the existing role modified.