topic Re: Different column in a table point to a single ID column in App Development

Different column in a table point to a single ID column

mansoorsheraz — Sun, 25 Dec 2022 15:43:10 GMT

Hi,

I am, making an app where I have the userID but that is being used in another table in three other columns. How can I make the load script so that selecting an employee can let me know how many projects he is supervising and how many he is reviewing etc. Below attached is the sample data model. Any help is appreciated. (AssignedTo, Supervisor, Reviewer all have the UserID value in them)

The End Result should be that when a filter on the UserName is applied, the KPI's for Assigned, Supervised, Reviewed should have the respective value in them.

e.g. if in the filter for Users "John" is selected then the KPI's should display:

Assigned Supervised Reviwed
0 2 0

Regards.

Re: Different column in a table point to a single ID column

sidhiq91 — Mon, 26 Dec 2022 07:42:35 GMT

@mansoorsheraz Please use the below code in the back end script first:

NoConcatenate
Temp1:
Load * inline [
JobID, Jobname, Assigned to, Supervisor, Reviewer
1,Apply Cement,1,2,4
2,Plumbing works,3,2,5
];

Temp2:
crosstable(UserID,Values)
load JobID,
//Jobname,
[Assigned to],
[Supervisor],
[Reviewer]
Resident Temp1;

Drop table Temp1;

NoConcatenate
Temp3:
Load JobID,
Values as UserID,
UserID as JOBTitle
Resident Temp2;
Drop table Temp2;

Left join (Temp3)
Load * inline [
UserID, UserName
1,Smith
2,John
3,Michael
4,Tom,
5,Laurel
6,Johnson
];

Exit Script;

In the Front End you can use something as below for Expression:

Supervised: Count({<JOBTitle={'Supervisor'}>} UserID)

Assigned: Count({<JOBTitle={'Assigned to'}>} UserID)

Reviewed: Count({<JOBTitle={'Reviewer'}>}UserID)

If this resolves your issue, please like and accept it as a solution.

Re: Different column in a table point to a single ID column

anat — Mon, 26 Dec 2022 09:44:02 GMT

Temp1:
CrossTable(deg, depname,2)
Load * inline [
JobID, Jobname, Assigned to, Supervisor, Reviewer
1,Apply Cement,1,2,4
2,Plumbing works,3,2,5
];

LOAD UserID as depname, UserName

;
Load * inline [
UserID, UserName
1,Smith
2,John
3,Michael
4,Tom,
5,Laurel
6,Johnson
];

Re: Different column in a table point to a single ID column

mansoorsheraz — Mon, 16 Jan 2023 19:45:33 GMT

@sidhiq91 @anat Thank you both for your solutions. But the issue that I am, facing is far more completed. These three columns AssignedTo, Supervisor, Reviewer , they come in other tables too e.g. Audits, Surveys, Tasks etc with the base table being the same i.e. tbl_users. How can I have them all coming up here? If I start to add in the the ID's from Audits, Surveys, etc synthetic keys start to come up as well. Can you please guide me?

Regards.