Skip to main content
M
MA873117416-Pearl
16-Pearl
July 10, 2024
Solved

Intersect and Derive Fields InfotableFunctions Error

  • July 10, 2024
  • 1 reply
  • 1105 views

DATASHAPE 01

Jamal8548_0-1720616259251.png

DATASHAPE 02

Jamal8548_1-1720616287846.png

My service:= I am having two infotables. In first infotable i have no Criteria key value in the Infotable and in second infotable i have this Criteria and now i am doing this with infotable functions. but i have an error
: Script evaluation error : ReferenceError: "Criteria" is not defined. (DSLCompiledExpressionProcessor#1)"

 

"

 

 

let TableDynamicData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
		infoTableName: "InfoTable",
		dataShapeName: "Jamal.DS"
	});

let TableDynamicDataTwo = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
		infoTableName: "InfoTable",
		dataShapeName: "JamalOne.DS"
	});


let jsonData = {

 "UserResponse": {
 "I_O": "",
 "Criteria": "",
 "Image": [],
 "Remark": "1688977680000"
 },
 "QID": "1_1",
 "I_O": "",
 "Image": [],
 "Remark": "1688977680000",
 "I_O_Image":"jamal"
 
 
};

TableDynamicData.AddRow(jsonData);
let jsonData2 = {


 "QID": "1_1",
 "Criteria": "Test Successful",
 
};
TableDynamicDataTwo.AddRow(jsonData2);


//let result = TableDynamicData;
	let fieldNamefromStaticTable = 'QID,I_O,Image,Remark,I_O_Image,UserResponse';
 let fieldNameForDynamicTable = 'Criteria';

let tempData = Resources["InfoTableFunctions"].Intersect({

 columns1: "fieldNamefromStaticTable",
 columns2: "fieldNameForDynamicTable",
 joinType: "LEFT",
 joinColumns1: "QID",
 joinColumns2: "QID",
 t1: TableDynamicData,
 t2: TableDynamicDataTwo,

});


		params = {
			types: 'STRING' /* STRING */ ,
			t: tempData /* INFOTABLE */ ,
			columns: 'Criteria',
			expressions: "(Criteria==undefined)?'':Criteria"
		};
		result = Resources["InfoTableFunctions"].DeriveFields(params);

 

 

Best answer by Velkumar

Hi @MA8731174 

 

Here you are passing wrong input

Velkumar_0-1720676587304.png

 

Based on your code, it should be Variable not Value

Velkumar_1-1720676627726.png

 

Modified Code :

let TableDynamicData = me.TESTQUERY2();

let TableDynamicDataTwo = me.TESTQUERY();

//let result = TableDynamicData;
let fieldNamefromStaticTable = 'customerName';
let fieldNameForDynamicTable = 'orderId,orderDescription,customerId';

let tempData = Resources["InfoTableFunctions"].Intersect({

	columns1: fieldNamefromStaticTable,
	columns2: fieldNameForDynamicTable,
	joinType: "LEFT",
	joinColumns1: "customerId",
	joinColumns2: "customerId",
	t1: TableDynamicData,
	t2: TableDynamicDataTwo,

});

let result = tempData;

params = {
	types: 'STRING' /* STRING */ ,
	t: tempData /* INFOTABLE */ ,
	columns: 'orderId',
	expressions: "(orderId== null) ? 0 : orderId"
};
result = Resources["InfoTableFunctions"].DeriveFields(params);

 

Velkumar_0-1720677121331.png

 

 

/VR

 

 

1 reply

V
19-Tanzanite
Velkumar19-TanzaniteAnswer
19-Tanzanite
July 11, 2024

Hi @MA8731174 

 

Here you are passing wrong input

Velkumar_0-1720676587304.png

 

Based on your code, it should be Variable not Value

Velkumar_1-1720676627726.png

 

Modified Code :

let TableDynamicData = me.TESTQUERY2();

let TableDynamicDataTwo = me.TESTQUERY();

//let result = TableDynamicData;
let fieldNamefromStaticTable = 'customerName';
let fieldNameForDynamicTable = 'orderId,orderDescription,customerId';

let tempData = Resources["InfoTableFunctions"].Intersect({

	columns1: fieldNamefromStaticTable,
	columns2: fieldNameForDynamicTable,
	joinType: "LEFT",
	joinColumns1: "customerId",
	joinColumns2: "customerId",
	t1: TableDynamicData,
	t2: TableDynamicDataTwo,

});

let result = tempData;

params = {
	types: 'STRING' /* STRING */ ,
	t: tempData /* INFOTABLE */ ,
	columns: 'orderId',
	expressions: "(orderId== null) ? 0 : orderId"
};
result = Resources["InfoTableFunctions"].DeriveFields(params);

 

Velkumar_0-1720677121331.png

 

 

/VR

 

 

    M
    MA873117416-PearlAuthor
    16-Pearl
    July 11, 2024

    Thank you for your detailed answer! I have fixed the problem at my side. Now i would like to write on it for clarification so that may be it will help new comers also. @Velkumar Please correct me if something is not right...?

     

    INTERSECT FUNCTION

    We use Intersect function to combine the rows from two infotables so if i have two infotables 

     

    infotable 1:- ID, customerName, Address

    infotable 2:- ID, orderId,orderDescription,customerId

     

    let fieldNamefromStaticTable = 'customerName';
    let fieldNameForDynamicTable = 'orderId,orderDescription,customerId';

     

     

    let tempData = Resources["InfoTableFunctions"].Intersect({

	columns1: fieldNamefromStaticTable,
	columns2: fieldNameForDynamicTable,
	joinType: "LEFT",
	joinColumns1: "customerId",
	joinColumns2: "customerId",
	t1: TableDynamicData,
	t2: TableDynamicDataTwo,

});

let result = tempData;

     

     

    Case 1: now even though in Infotable 1 we have address but Intersect will not return us this because from that infotable we have mentioned that we need 'customerName' only and from second one we mentioned that we need  'orderId,orderDescription,customerId' So it will only return these 4 properties.

     

    Case 2: If any one of the entry from both infotables would NOT have orderId means null like orderId:"" then we will get the result of only from first Infotable like above orderId and customerName we got for the last entry but we have to write a condition in DeriveField. Because we are merging/reading the values on behalf of IDs as we have mentioned them in intersect function.

     

     

     

    Case 3: We have write the field name from Infotable 1 if we want it also in the result otherwise we could not get it. We only get the fields which we will mention only as we did in fieldNamefromStaticTable and fieldNameForDynamicTable 

     

    Case 4: We can write as many times the DeriveFields function to add more conditions 

     

    params = {
	types: 'STRING' /* STRING */ ,
	t: tempData /* INFOTABLE */ ,
	columns: 'orderId',
	expressions: "(orderDescription== order1) ? 23 : 55"
};

     

     

    as above i write an addition condition again so that i can manipulate the orderDescription only for order1 so it finds this then it  will assign it 23 otherwise 55 or orderDescription what we are getting there. 

     

    I have tried myself to write few conditions to cover all aspects of these two functions to clear my confusions and for future developers. Please correct me if something is not right or clear. If you have any additional usecase in mind please let me know. 

     

     

    Terms & ConditionsCookie settingsAccessibility statement