FIltering Table Help

FIltering Table Help

  

Hey, guys, I need your help yet again and I am not sure if this is something i have to use SQL for or if OutSystems can do it.


I need to be able to see which employee is in Online Training, well this issue is solved by filtering only those that have an Online Training Status set at that time. However, this employee can have dozen of OnlineTraining records. I need to only see the most recent training record added by the Training Department (One Record).

How do I see only one record, instead of all records, for an employee set to Online Training status? 

--------------------------

So basically I am creating a LIST of all employees that have their Status set to Online Training. However, the Table displays ALL of their previous training for Online Training. I only need to see ONE entry for each employee that has their status set to online training as this will be their most recent training entry.

I attached the Program to the conversation - this issue is in Online Training Progress webscreen. 


Hello Jared,

If you only want to know each employee is on that table you can do a group by EmpsId? You get one record for each EmpsId.

Hi Jared,

and also if you need only one latest record, after make a group by Employee Id suggested by EFreitas, you may create new attribute (Max training record ID) to fulfill this requirement:

"I need to only see the most recent training record added by the Training Department (One Record)."

Well I need to see only one record from EACH employee that has their status set as Online Training.

So I have it filtered to show only those with the Status of OnlineTraining, it's just showing all the Online training records from each employee's past that has their status as OnlineTraining. I just need it to show the most recent training record entered by the training team for each employee that has their status as OnlineTraining.

Grouping by EmpsID is the best way for this?

Hello Jared,

If the records are added by the training team, it means that the record with the highest Id is the latest online course that is assigned to an employee. If this in mind, you can group by EmpsID and then find the MaxId (like Imelda said). 

In this example, you can notice that the Customer 6 have 2 Courses assigned to him and the output will be only one. The Customer "6" is doing the course "3".

I used Max ID on the Process "Order" number because I have it were the order is 12345 same with their Process Numbers they have learned, so if they have learned three processes then the new process will be Process 4 and Order number 4. I set the Max Id to use the Prcoess Order Number. 

It's working, but it's just showing the Order Number instead of the process Label.



When you do a group by, you only have that attribute as an output. You need to group by the others attributes that you need. This should work for your problem.

If you can't do this way you can use the Ids from the GetEmployeeOnlineTrainings aggregate and get the remaining data.

EFreitas wrote:

When you do a group by, you only have that attribute as an output. You need to group by the others attributes that you need. This should work for your problem.

If you can't do this way you can use the Ids from the GetEmployeeOnlineTrainings aggregate and get the remaining data.

The Order Max is only displaying the Process Order Number (4) instead of the Label (Process 4). Hmmm


Below is what i Have in my aggregate currently and it's the only aggregate in the Prep.


Grouping by AOD Name fixed my name issue, however, i still need to display the Label which should be "Process 4", I also need to show efficiency. So I should group the Labels and Efficiency to get them to show? 


the easiest way is to have 2 aggregates. On the first you only need to get the EmpId and the last courseID. Than you build a local list where you append all the information you need based on the first aggregate.

You do a cycle, where for each row (that has a EmpID and a CourseID), you fetch the labels that you need to show on the table.

Hi Jared,

I would advise using a SQL Node and getting the info you require directly from the DB without the need of additional processing.

Something like 

Attention that this was not tested and should be optimized using SQL Nodes best practices.



Cheers,

Tiago.

I tried the SQL and I just could not get it to work. I believe it's currently just too advanced for me to do through SQL. I understand how to operate with SQL, but I just don't know enough about the language to get it do what I need.

For Example i know things like below


SELECT Name, LastName, City, DateOfBirth

FROM Table

WHERE City = Table


Or 


SELECT Genre

FROM Table

ORDER BY Genre


or SELECT FirstName || ' ' || LastName and so on.


Any other ideas? :(

Solution

Hello Jared,

is this what you want?
https://emanuelfreitas.outsystemscloud.com/TestWithSQL/

The table that I think you want is on Home screen and you can see the data on Emps screens. I have some emps that are doing online training and others In-Process. Some of the emps have more than one training assigned.

I attached an zip with an .oml and a excel file where you can see the data that I bootstrap.

I added some comments inside the SQL query. If you receive "query return no rows." confirm if you have an input value assign, as you can see on the following pic:


Play with the app and see if this works for you.

Solution

Hello Jared,

What about a small change in your data model and logic?

If the employee can do a single online training at a time (seems the case), you can just add a boolean attribute (non mandatory, try by default) to the EmployeeOnlineTraining entity. Every time the department assigns a NEW training, you set the old ones to False.

This way, in this screen, you could simply join Emps and EmployeeOnlineTraining and filter by the Emps.TrainingStatusId = OnLineTraining and by EmployeeOnlineTraining.YourBooleanField = True

Cheers.

Thank you both so much for helping me. I cannot express my appreciation for the amount of patience shown by all of you. I think it's almost there. I am trying to figure out what's going on with this error code at the moment related to implementing the SQL. I am sorry I did not respond sooner, but had summer shut down all last week.

[1] Error executing query.
   at ssDivue_Core.Flows.FlowMainFlow.ScrnOnlineTrainingProgress.FuncssPreparation.QuerySQL1(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, String qpstInOnlineTrainingStatus)
   at ssDivue_Core.Flows.FlowMainFlow.ScrnOnlineTrainingProgress.Preparation(HeContext heContext)
   at ssDivue_Core.Flows.FlowMainFlow.ScrnOnlineTrainingProgress.Page_Load(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

[2] Error in advanced query SQL1 in Preparation in OnlineTrainingProgress in MainFlow in Divue_Core (SELECT {Emps}.*, {Processes}.*, {NumOfProcesses}.*  /*output*/  from {Emps}  inner join {EmployeeOnLineTraining} on {EmployeeOnLineTraining}.[EmpId] = {Emps}.[Id]  inner join {Processes} on {EmployeeOnLineTraining}.[ProcessId] = {Processes}.[Id]  inner join {NumOfProcesses} on {EmployeeOnLineTraining}.[NumOfProcessId] = {NumOfProcesses}.[Id]  where 1 = 1      and {Emps}.[TrainingStatusId] = @InOnlineTrainingStatus  /*filter by Online Training*/      and {EmployeeOnLineTraining}.[Id] = (                    /*get the last record for each EmpId*/          select max({EmployeeOnLineTraining}.[Id])          from {EmployeeOnLineTraining}          where {EmployeeOnLineTraining}.[EmpId] = {Emps}.[Id]      )  order by {Emps}.[LastName]                                 /*order by First Name*/): Could not assign 'Eric' to 'Processes.Order'... Input string was not in a correct format.


I'm just not sure how the Input String was in an incorrect format. Hm. I've link the new OML. The issue is in OnlineTrainingProgress in the Divue_Core module if anyone has time. I hate to keep harping on this question.



Unexpected SQL

There is a mismatch between the SELECT'ed entities and the output structure in SQL1.


I am also getting this warning, but all the Selected entities are also in the Output structure. Hm

Hello Jared

Can't open your app right now.
But if the platform says there is a mismatch between your SELECT and the Output structure, there is.

I would point to the order of the Output Structure. Afaik, the order must be the same, so, start with Emps, than Processes and finish with NumOfProcesses. You probably are starting with Processes instead.

Cheers.

P.S. Fetching all fields is not good practice if you don't need all of them...

WOW! It was the order in the OutSystems Output Entities / Structures ... all day over the order. I had one Table out of Order. Thank you Eduardo.