Ad-Hoc Report Categories | IFS Community
Question

Ad-Hoc Report Categories

  • 11 March 2021
  • 2 replies
  • 112 views

Userlevel 3
Badge +7

Hi

How can I connect the Place table to the Opportunity table for ad--hoc reporting purposes?

I have created an ad-hoc report category called Sales and added both the Place and Opportunity tables to it. However, when creating a report from this category I get errors when combining the Place Name with the Opportunity ID and Description. The error is:

 

Any ideas?


This topic has been closed for comments

2 replies

Userlevel 7
Badge +18

Hi @PirtekKH,

The Opportunity and Place tables share the PlaceID value so the two tables can be joined on that value. You can then pull through any values you need from Place.

Name cannot be used as it is not a unique key field and could contain multiple places with the same name.

Kind regards,

Lee Pinchbeck

Userlevel 3
Badge +7

When creating ad hoc reports, we understood that we need to consider parent child relationships when adding fields. E.g.: If we are using Request, Task and Place, the following tables are needed to create the adhoc report.

The parent table will be REQUEST, and primary key will be Request_id. Hence Request_id from Request table should be added first.
Next, the columns of TASK and PLACE tables, which are child tables of REQUEST table, need to be added. Note that each table should be mapped directly to the parent table with the primary key (request_id).
Steps
1. Add the Request_id field from REQUEST.
2. Add the Task_id field from TASK.
3. Preview. Data will populate
4. Add more fields from TASK table.
5. Add fields from place table (as place_id of PLACE table is mapped with place_id of REQUEST table)


Via that guideline most columns / tables can be added without getting the error message but we are still facing some other issue that some columns are not filled with data - see the following conversation