List Manager Forum

I need help on joining tables value, please

User aliwari 2022-04-19 14:54:13

Hi MSteam

Could you please tell me how can I make connection for column/value on 2 tables

For example I have a table1 with 3column value (user_id as a text type value),( on_off as option list type value with 2 options in it On/Off), and (owner_id as user type value), on the other hand I have a table2 with 3column value as well (user_id as an option list type value + applied SQL query under the option list for the user_id/table2 as "select user_id as id, user_id as value from table1 where user_id= ##id_user##"and (owner2_id as user type value).

*table1/user_id has 3 record each one has a value "sara", "sara1", " sara2", and for on_off column value is set as "on" for both "sara" and "sara1" and set to "off" value for "sara2".
when I create new record in table2 I can see a list menu in the filed of user_id showing the 3 sara from table1 which is working perfect.

my issue is how can I make it to show "on" in table2 recored after I choose "sara" or "sara1" from the list? or to show no if i choose "sara2" since On/Off is can be modified in table1 only


your help is appreciated



Regards

Ali

Moonsoft support 2022-04-20 10:02:21

Hi,
in order to show a field with the result of a query based on the same row data, you can set that column as 'calculated query' type. Then at the configuration tab 'data/values' scroll down to Only for Calculated Query Type->calculated query. There you can type a query extracting your desired result, from any table or join combination across your database, and you can use ##yourcolumn## syntax in order to use any data of the actual row inside the calculated query.


Hope this helps

User aliwari 2022-04-24 18:32:22

Hi ,

I'm still trying on it, I applied so many way of the query but I had no luck to get the value to be change on one row, I used ##yourcolumn## syntax in order to use the data of the actual row inside the calculated query. but I had no luck too!

for example :

1- select column_id as id, column_id as value from table1 where column_id= ##column_id##

2- SELECT
COLUMN_ID,
SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as id,
SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as value
FROM TABLE where COLUMN_ID=##column_id##
GROUP BY COLUMN_ID

all the ways does not show the specific value that showed be showing for the individual row , it will show all the collected data as its not filtered to the ##column_id## specified

is there any advice can be appreciated ?



Regards



Ali

Moonsoft support 2022-04-25 09:42:55

Hi,
we're not sure where do you see several values being shown, because if you set the column type as 'calculated query', only one single value is returned to be printed inside the cell . Please note you shouldn't show this field type inside the user input form, because you're configuring it to be automatically calculated afterwards and you should only show it at the main table layout. You can return only one single value at the 'calculated query' setting, like this

select column_id from table1 where column_id= ##column_id##

(here you should get a column being a 'copy' of your column_id)

depending of the type of the 'column_id', you may need to use it inside '' to get a right sql query

select column_id from table1 where column_id= '##column_id##'

If you're still having problems please let us know where do you see the multiple values and we'll try to reproduce here.

User aliwari 2022-04-25 21:07:27

Hi,

The query' setting (select column_id from table1 where column_id= '##column_id##') working now ... I think i was missing (' ') Thank you so much...


BUT the returned printed value inside the cell shows database value, how to let it show the screen value instead?

I tried to change query' setting as (select column_id as id, column_id as value from table1 where column_id= '##column_id##') beside that, I did apply id/value in the option list configuration but the printed result still showing database value not the screen value.




Regards



Ali

Moonsoft support 2022-04-26 09:37:29

Hi,
the calculated query will return the value you are selected with your query. If you select column_id, then you will get this column_id, as commented above, this query
select column_id from table1 where column_id= '##column_id##'
will only return a 'copy' of your column_id, if you want to show another value you will need to select a different column as your result.


Regards

...
List Manager

Build different lists for your site

Buy now!
...
Support/development

Perfect for small code changes or to correct any bug at your site

Buy now!