Column displaying value of a third table with fusion table between them

[gynl]

Hello,

New to the world of Totum, I'm stuck on an issue — likely obvious to you — for which a tip would be helpful.

I have three tables. In a simplified manner, it looks like this:

User [id, name]
Device [id, name]
Attribution [id, user_id, device_id, date_start, date_end]

The idea is to track my equipment inventory and know who owns what.

I would like to add a column in the "Device" table that displays the name of the person currently using it.
I already display the attribution, which I can update and add from "Device," but I can't seem to retrieve the name of the "User."

In essence, in SQL, I would do:

SELECT User.name 
FROM Device
LEFT JOIN Attribution on Device.id = Attribution.device_id
LEFT JOIN User on User_id = Attribution.User_id 
WHERE Device.id = #id 
AND Attribution.date_end = NULL

I assume I should use a selectRowListForSelect with a sub-function for filtering, but I'm not making any progress.

Could someone help me out?

Thank you !

[erpic-ru]

Hi! in your case you can use simple "Select" function in Code section in Device table:

= : select(table: 'attribution'; field: 'user_id'; where: 'device_id' = #id; where: 'date_end' = ""; )

also you should recalculate Device table manually or automatically. For example in action code on add, edit and delete in Attribution table (field device_id):

a1= :reCalculate(table: 'device'; where: 'id' = $#nfv)
ch1= :reCalculate(table: 'device'; where: 'id' = $#onfv)
dl1= :reCalculate(table: 'device'; where: 'id' = $#onfv)

[gynl]

Ok ! Juste understood I can do

= : select(table: 'tm_personnel'; field: 'canonical_name'; where: 'id' = $uid)
uid : select(table: 'attribution'; field: 'receveur'; where: 'id' = #attribution ; )

to make an kind of INNER JOIN.

Thank you !