Reporting: Use of Generic Inquiries as Data Sources for a Generic Inquiry

In previous versions of MYOB Acumatica, only data access classes (DACs) could be specified as data sources for a generic inquiry. The technical specialist (that is, the user developing the generic inquiry) then joined the added DACs one by one through key fields to construct a query. Some inquiries could have multiple tables joined into very complex queries.

MYOB Acumatica 2024.1.1 simplifies the process of constructing a query. Now a technical specialist can specify generic inquiries as data sources for a generic inquiry. That is, they can create multiple simple generic inquiries and reuse them as data sources for a generic inquiry. The specialist build a complex request by combining the simple inquiries into one.

Adding a Generic Inquiry as a Data Source

In MYOB Acumatica 2024.1.1, the following elements have been renamed or added on the Generic Inquiry (SM208000) form:

The Tables tab has been renamed to Data Sources (see Item 1 in the following screenshot).
The Table Name column in the table of this tab has been renamed to Source Name (Item 2).
The Generic Inquiries tab has been added to the lookup table for the Source Name column (Item 3).

Figure 1. User interface changes on the Generic Inquiry form

Important: On the Generic Inquiries tab of the lookup table, the system displays (that is, makes available for selection) only inquiries that are not using the current inquiry as a source.

When a specialist uses generic inquiries as data sources for other inquiries, the system does not limit the number of levels of nesting. It ensures only that an inquiry is not a parent to another inquiry that uses it as data source.

Tip: The term parent refers to a generic inquiry that uses another generic inquiry as its data source.

To add a generic inquiry as a data source, a technical specialist clicks Add Row on the table toolbar of the Data Sources tab. In the new row, the specialist opens the lookup table for Source Name. On the Generic Inquiries tab of the lookup table, the specialist selects the needed inquiry. The system adds the inquiry to the table and switches focus to the Alias column. The specialist can type an alias for the inquiry or skip this step. The system inserts the value from Source Name if no alias has been specified.

If the added generic inquiry is a parent to an inquiry that in turn is a parent to an inquiry (and so on in a chain of relations), each contained inquiry is considered a data source for the selected inquiry. However, the system does not add all the nested inquiries to the table of the Data Sources tab, only the selected one.

For each added data source, the technical specialist needs to specify the relations between pairs of data sources (tables or inquiries) on the Relations tab. For each pair of related data sources added to the Table Relations table, the specialist specifies the links between these two data sources in the Data Field Links for Active Relations table. If a field from the source inquiry is not stored in the database, it cannot be used to define a relation.

Tip: The Related Tables dialog box, which can be used to add relations between tables, is not available for adding relations between inquiries. When a technical specialist selects a row with an inquiry in the table on the Data Sources tab or in the Table Relations table on the Relations tab, the Add Related Table button becomes unavailable.

Adding Fields from a Source Inquiry to the Results Grid

When a technical specialist adds a generic inquiry as a data source on the Generic Inquiry (SM208000) form, the system does not add its fields to the Results Grid tab of the form. The specialist should manually add the needed fields.

When the specialist adds a field from the source inquiry on the Results Grid tab, the system copies the following settings from the source:

Use in Quick Search
Quick Filter
Caption
Width
Schema Field
Style Formula

For the rest of the settings the system inserts default values. The system modifies the copied settings to include the source inquiry name in the field names used in these settings. For example, if a field from source inquiry A is used in a style formula, the system will modify the field name to A.field_name.

The system does not copy the navigation settings for the fields from the source inquiry. However, if such a field has customized navigation settings (that is, different from the default behavior), the system will use these settings. These settings will apply for all parent inquiries where this field is added to the Results Grid tab. The specialist can modify the navigation settings for the current inquiry to achieve the needed behavior.

Using Parameters from a Source Inquiry

The system does not add parameters from source inquiries automatically to the Parameters tab of the Generic Inquiry (SM208000) form. A technical specialist can add any needed parameters manually. As with the field names, the system will display parameter names with the alias of the source inquiry preceding the parameter name.

When the specialist adds a row on the Parameters tab and selects a parameter from a source inquiry in the drop-down list in the Name column, the system adds the parameter and copies its settings. If the specialist first types some name in the Name column and then opens the drop-down list in this cell and selects a parameter from a source inquiry, the system adds the parameter with the typed name and the settings from the selected parameter.

When parameters in the parent and source inquiry share the same names, the system creates a link between these parameters. The specialist can adjust the parameter settings. However, these modifications must align with the settings in the source inquiry. For instance, if a parameter is defined as an integer type in the source inquiry, it should not be redefined as a string.

Modifications made in the parent inquiry do not affect the linked source. However, if a specialist modifies a parameter in the source inquiry, the system will show a warning indicating that the changes will affect the parent inquiry.

If a specialist changes the name of the parameter with the linked source, the system removes the link between these parameters. If the specialist reverses the name change, the system restores the link.

Application of Access Rights

A user with access rights to view a generic inquiry will see all the data that this inquiry returns, regardless of the user's access rights to the source inquiries that are included in the query.

If data from the source inquiry is restricted through the use of restriction group settings, the system may limit the display of data based on these settings.

Modifying an Inquiry Used as a Data Source

After a generic inquiry has been added to the table on the Data Sources tab of Generic Inquiry (SM208000) form, its name is displayed as a link. If a technical specialist clicks the link, the Generic Inquiry form opens in a new browser tab with the inquiry selected. The system will display a warning indicating that the inquiry is used as a data source for another inquiry. If a specialist modifies the inquiry used as a data source, the system opens the Copy Generic Inquiry dialog box (see the following screenshot). The dialog box asks the specialist if they want to create a copy to save modifications (which leaves the original inquiry intact) rather than overwriting it.

Figure 2. The Copy Generic Inquiry dialog box

If the specialist clicks Copy, the system saves a copy of the original inquiry with the modifications; it adds after the original value in Inquiry Title a hyphen and a number. The system increments the number each time a copy of that inquiry is saved.

Deleting an Inquiry Used as a Data Source

A technical specialist may want to delete an inquiry that is used as a data source by opening this inquiry on the Generic Inquiry form and clicking Delete on the form toolbar. The system displays a warning that lists the inquiries that use this inquiry as a data source.

If the specialist chooses to delete this inquiry, the system will mark its fields with a warning when the specialist opens a parent inquiry on the Generic Inquiry form. Suppose, a field from the deleted source inquiry was earlier added on the Results Grid tab of the parent inquiry. When the specialist opens the parent inquiry (after deleting the source inquiry), the system will display a warning in the row with the field from the delete source inquiry on the Results Grid tab (see the following screenshot).

Figure 3. The warning about the deleted field

Similarly, if a specialist deletes a field on the Results Grid tab of a source inquiry that is somehow used in its parent inquiry, the system will display a warning for the deleted fields when the specialist opens a parent inquiry on the Generic Inquiry form.

Exporting and Importing Inquiries

In MYOB Acumatica 2024.1.1, when a technical specialist exports a generic inquiry that has other inquiries as data sources, the system includes all the inquiries from the chain of sources in the export file.

Also, when a specialist imports a file with generic inquiries and inquiries in the system have the same identifiers, the system displays a warning with the list of the inquiries. The specialist can cancel the import, or they can confirm the import of the inquiries, causing the system to overwrite the inquiries.