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.

Action Worksheet
Action Worksheet


You can find the Action Worksheet in menu Data Tools or access it from the project- or transport card.
  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
    1. Example SQL Query to Change Data
    2. Example SQL Query to Add a View
  11. Execute DOS Command
  12. Execute PowerShell Command
  13. Save Options

Action Types
Copy DataCopy data between tables or companies
Delete DataDelete records in a table or empty fields
Transfer DataTransfer data between databases
Fill FieldsFill fields with values
Run ReportRun a report
Run CodeunitRun a codeunit
Rename DataRenames data
Renumber ObjectRenumbers an object
Renumber FieldRenumbers a field
Execute SQL QueryExecutes a SQL query
Execute DOS CommandExecutes a DOS command
Execute PowerShellExecutes a PowerShell Script

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.

Setup
Setup


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.

    Select the Fields to Copy
    Select the Fields 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.

    Select the Into Fields
    Select the Into Fields
  4. Press Start to get the result.

    Start
    Start


    Address Copied to Address 2
    Address Copied to Address 2

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.

Delete Data
Delete Data


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

    Transfer Data
    Transfer Data
  4. The Object Manager Exports the action + data as a FAB file
  5. Save the file

    Save FAB File
    Save FAB File
  6. Open the Customer database
  7. Open the Action Worksheet
  8. Press Import and Open the FAB file

    Import FAB File
    Import FAB File
  9. The Action is imported in the Action Import Worksheet
  10. Press Start

    Import Data
    Import Data
  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

    Fill Fields
    Fill Fields
  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.

Run Reports and Codeunits
Run Reports and 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.

Copy Renumber Actions to Project
Copy Renumber Actions to Project

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.

Copy Renumber Actions to Project
Copy Renumber Actions to Project

Execute SQL Query

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

Edit SQL Query
Edit SQL Query


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

Save SQL Query
Save SQL Query

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.

Linked Object
Linked Object


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.

Restarting a NAS
Restarting 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.

Execute PowerShell
Execute PowerShell


Save Options

Save Options
Save Options

DELETEALL;With this option the table will first be emptied before the action is executed.
INIT;Every new record is first initialized before the action is executed.
IF FIND(‘=‘) THEN;
  • True - If this option is enabled the action first reads the key fields into the new record and then tries to find the existing record. If found the existing record will be modified, otherwise a new record will be inserted (also depending on the next two options "IF INSERT THEN;" and "IF MODIFY THEN;").
  • False - If this option is disabled a new record will be used if used in combination with "INIT;". The previous record will be used if the "INIT;" option is also disabled.
IF INSERT THEN;If disabled, no new records will be created.
IF MODIFY THEN;If disabled, no existing records will be modified.
Commit TypeIndicates how many times a commit is executed.
  • <EMPTY>: No committing is done. Only when all actions are executed
  • At the end: Commit is done when this action is executed.
  • After each record
  • After 100 records