Dynamically create relationships

[fridaystreet]

Is there a way to define relationships in the schema or options prior to a datasource being used.

The scenario is as follows:
We have a custom datasource adapter which we add programatically to the editor. In the code for that data source we have around 5 tables. Some of these tables have related data.

We have a process function that handles the datasource requests to provide the schema and data as per below

Code: Select all

const schema = {
      success: true,
      types: {
        Task: {
          id: 'string',
          'domain.id': 'string',
          'domain.id.name': 'string',
          name: 'string',
          description: 'string',
          parent: 'string',
          'assignee.id': 'string',
          'assignee.displayName': 'string',
          'createdBy.id': 'string',
          'createdBy.displayName': 'string',
          deadline: 'datetime',
          finsihedAt: 'datetime',
          createdAt: 'datetime',
          updatedAt: 'datetime',
          taskNumber: 'string',
          priority: 'string',
          estimatedEffort: 'int',
          actualEffort: 'int',
          progress: 'int',
          status: 'string',
          colour: 'string',
          tags: 'array',
          'counters.notes': 'int',
					'counters.attachments': 'int',
					'counters.subtasks': 'int',
					'counters.completedSubtasks': 'int',
					'counters.checklistItems': 'int',
					'counters.completedChecklistItems': 'int',
          'schedule.start': 'datetime',
          'schedule.end': 'datetime',
          'context.type': 'string',
          'context.id': 'string'
        },
        User: {
          userId: 'string',
          userName: 'string'
        },
        Domain: {
          id: 'string',
          name: 'string',
          description: 'string',
          goals: 'object',
          parent: 'string',
          pathToTLD: 'array',
          role: 'string',
          tld: 'string',
          subdomains: 'array',
          tags: 'array'
        }
      }
    }
const process = async ({ command, queryString, connectionString }, callback) => {

    if (command === 'RetrieveSchema') return callback(schema)
    if (command === 'RetrieveData') {
      const data = await getData(queryString, connectionString)
      callback(data)
      return
    }
    return callback()
  }

The task and domain tables have a relation to associate the domain the task is in to the domain details in the domain table.

When a user wants to use this datasource, they add it from the list of datasources and they provide an id in the connection string of the domain they would like the task data for. So the data source can retrieve the tasks from the api. It also retrieves the domains table containing the details of all the domains the tasks might be in.

I found a post outlining how to had a relationship to a dataset like below, but this needs to happen on the actual dataset on the report. So would require there to be a data set already added to the report.

Code: Select all

var dataRelation = new Stimulsoft.Report.Dictionary.StiDataRelation("Relation", "Relation", "Relation", report.dictionary.dataSources.getByName("root_scopeOfWorks"), report.dictionary.dataSources.getByName("root_scopeOfWorks_scope"), ["id"], ["headerId"]);
report.dictionary.relations.add(dataRelation);

So is there either a way to
1) predefine the relationships in the datsource (schema) itself so when it is added and data fetched, like it pulls the fields from the schema it would already have the relatsionships
or
2) automatically setup the relations at the point in time when the datasource is added to the report, ie through the 'process' function above or something?

Any assistance is greatly appreciated

[Lech Kulikowski]

Hello,

In the JSON data source, it is not possible to predefined relations, its added automatically only for the nested objects.
For the XML, you can specify them in the XSD schema file.

Thank you.

[fridaystreet]

Hi Lech,

Thanks for the prompt response. That's awesome if you can, we can easily manipulate the data coming from the api before it's passed to the editor, in fact we already do in order to flatten it into the shape of the schema. Can you provide some examples or a link to some examples please? How do we tell the schema the nested domain object in the task data is a Domain in the schema?

would we need to somehow specify it in the schema eg

Code: Select all

schema: {
   Domain: {
     ....fields
   },
   Task: {
      domain: Domain
   }
}

Regards
Paul

[fridaystreet]

I've tried doing the following with the schema and nesting the data returned from the api for the tasks table with the variosu data objects (fields with 'object' as type in schema. But it doesn't create any relationships and now when I add the data source it only shows the top of the object as the field. eg task.domain, but non of the subfields of domain. If I view the data I just get '[object ovject]' in the domain field. I can manually add 'domain.id' as a field, but that isn't the desired outcome.

Code: Select all

{
      success: true,
      types: {
        Context: {
          id: 'string',
          type: 'string'
        },
        Schedule: {
          taskId: 'string',
          startTime: 'datetime',
          endTime: 'datetime'
        },
        Counters: {
          taskId: 'string',
          notes: 'int',
	  attachments: 'int',
	  subtasks: 'int',
	  completedSubtasks: 'int',
	  checklistItems: 'int',
	  completedChecklistItems: 'int'
        },
        Task: {
          id: 'string',
          domain: 'object',
          name: 'string',
          description: 'string',
          parent: 'string',
          assignee: 'object',
          createdBy: 'object',
          deadline: 'datetime',
          finsihedAt: 'datetime',
          createdAt: 'datetime',
          updatedAt: 'datetime',
          taskNumber: 'string',
          priority: 'string',
          estimatedEffort: 'int',
          actualEffort: 'int',
          progress: 'int',
          status: 'string',
          colour: 'string',
          tags: 'array',
          counters: 'object',
          schedule: 'object',
          context: 'object'
        },
        Domain: {
          id: 'string',
          name: 'string',
          description: 'string',
          goals: 'object',
          parent: 'string',
          pathToTLD: 'array',
          role: 'string',
          tld: 'string',
          subdomains: 'array',
          tags: 'array'
        },

      }

[Lech Kulikowski]

Hello,

Please check the following structure:

Code: Select all

{
	"Master": [{
		"ID": 1,
		"MasterName": "Name1",
		"NestedObject": [{
				"NestedName": "NName11"
			},
			{
				"NestedName": "NName12"
			}
		]
	}, {
		"ID": 2,
		"MasterName": "Name2",
		"NestedObject": []
	}, {
		"ID": 3,
		"MasterName": "Name1",
		"NestedObject": [{
				"NestedName": "NName31"
			},
			{
				"NestedName": "NName32"
			}
		]
	}]
}

After synchronizing in the Dictionary, the relation will be added automatically.

Thank you.

[fridaystreet]

Sorry I still don't really follow? That's how we already have our data structured with the items nested inside.

are these just references? where is the rest of the data for each nested item? or does each nested item contain the full nested object (which ours currently does)

{
"NestedName": "NName31"
}


'After synchronizing in the Dictionary, the relation will be added automatically'. is this a manual thing or we need to put this into the custom process function that fetches the data somewhere? We don't really want users to have to do this manually after they import the data

cheers

[fridaystreet]

I've tried doing the sync manually but it still doesn't create the relationships.

Is there something specific with the naming convention within the data?

[fridaystreet]

Can you please have another read of the original post and just confirm you're definitley doing the same thing we are please? as it looks like from your screenshot you're using a JSON datasource.

from original post:

'The scenario is as follows:
We have a CUSTOM DATASOURCE ADAPTER which we add programatically to the editor'

Can we please get advice on how to automatically relate the data?

Regards

[Lech Kulikowski]

Hello,

We do not know how your data adapter is implemented. But in the data adapters its not added automatically.

You can add relations with the following code:
var dataRelation = new Stimulsoft.Report.Dictionary.StiDataRelation("MyRelation", "MyRelation", "MyRelation", report.dictionary.dataSources.getByName("Categories"), report.dictionary.dataSources.getByName("Products"), ["CategoryID"], ["CategoryID"]);
report.dictionary.relations.add(dataRelation);

Thank you.

[fridaystreet]

Hi Lech,

Thanks for reviewing it again, I figured as much and that was the original question, I quoted the code you've provided in my original post. The question is how do we use that code to add this relationship dynamically when the user adds a new datasource to the report selecting the custom data adapter as the source? The report isn't preloaded with the datsource already

Do we do it inside the data adapter process function? or is there some sort of 'datasource added' event we can hook into an check it's our custom one and automatically apply the relationships?

or is it a case that when we create the report we can run that line to add the relation even if the datasource doesn't exist and when they add it it will use the pre-existing relationship?

Regards
Paul