Auto filtering database field

[Andrzej]

Hello,

I need some help to filter/narrow down the output from the 'database advance field'.

I tried several ways like 'Applying filter on a field' or using smart classifier but it's still not working as expected.

Let's say I have some workers and I need to assign the proper manager to the given employee. How can I do it?

My sql statemant to retrives all employee (based on selected employee I need to assigne proper manager).

The scond field shoud also be dropdown or auto assigning value.

[Alex Medved _ConfiForms_]

Are you able to create your SQL query for the DB field in a way that it also selects a manager from the database?

Something like (pseudo SQL)

SELECT employeeId as id, employeeName, managerId FROM...

Possible?

[Andrzej]

yes, not problem at all 

Select DISTINCT [Employee Name] as id, [Employee Name] + ' (' + [Employee MPK] + ')' as Employee, [Employee Manager] as ManagerID from Employee

[Alex Medved _ConfiForms_]

In this case you already have the data in the first field

Assuming you call it employees (ConfiForms Field name)

Then you can always access the manageID via (ConfiForms Field macro, for example)

employees.managerid

Or via (in expressions)

[entry.employees.managerid]

 No need for an extra field (definition)

[Andrzej]

something like on the below screen? Sorry for question like this but I'm noob in this tool yet

[Alex Medved _ConfiForms_]

No, sorry.. absolutely not

You really DO NOT need an extra ConfiForms Field Definitiuon for the manager

You already have this data accessible through the "employees" field

When you want to view your data, for example through the TableView

You can add a ConfiForms Field macro with a field name set to be employees.managerid to access manager id value (directly through the employees field)

[Andrzej]

Where can I put such expression in ConFiForms Field macro?

And will it be visable in the form and also table? As for now I'm not able to see this field with ManagerID in the form/table while using ConFiForms Field macro

[Alex Medved _ConfiForms_]

You put there 

WybierzWakat.managerid

[Andrzej]

Still dosen't appear in the form and table at all 'ConFiForms Field macro' and not filtering. :/

Here is the set up again:

[Alex Medved _ConfiForms_]

Sorry, what should I be able to read from that picture?

It should be WybierzWakat.managerid, not WybierzWakat.ManagerID

See the description for the database field

Also, take a look at https://wiki.vertuna.com/display/TEST/Database+dropdown+with+extra+fields+in+a+table 

It shows how the extra field "link" is accessed...

Same approach I was suggesting for you

[Andrzej]

ok, I think i made one step forward. This is not displaying as I'm not placing ConFiForms Field macro in the TableView but in ConfiForm Form Definition. 

But when I place the form with the employees.managerid info in the table view my form is limited only to this column. Do you know maybe how to work around this?

[Alex Medved _ConfiForms_]

You will need to put ConfiForms Field macros for every other field you want to show in a view (TableView or any other)...

[Andrzej]

thanks a lot, really 

[Sahir Maharaj]

Hello @Andrzej

To dynamically filter and assign the appropriate manager to a selected employee, you can create a dependent dropdown field or use a smart classifier in your database interface.

I recommend to write a SQL query that retrieves the manager based on the employee’s department or role, ensuring the output is limited to relevant records.

If this setup isn’t functioning as expected, double-check the query logic and ensure the filtering criteria match the database schema.

[Andrzej]

Hello @Sahir Maharaj can you tell me more or less how the sql query should looks like inside this smart classifier? How can I refer to another field from the form in such sql query? 

And dependent dropdown field is called in this case 'Dynamic dropdown (Database)'?

Here also I should use sql but I don't know how to refer to the existing field and filter based on the earlier entered value.