Topic: SQL Report ID from...

Hello, i have an SQL Report that is working wonders, but i want to change one thing: I want to get two different id's, the main id from the technical maintance , i get this id from maintance.id=$id , but i also want to get another id from a second selected tablegrid, this tablegrid doesn't have a relationship with the maintance.id, it's independent.
How can i choose it's id? Tablegrid.sqlvalue doesn't seem to work on sql report, is there a way?

Re: SQL Report ID from...

nevermind, i found it , it works the same way as the examples for the comboboxes, i uses {tablegrid} on my report sql sentence, and it finds the new id for the new table. i just had to select the names from the table where id = tablegrid

Re: SQL Report ID from...

ah... but i have found a small problem with  my query.

I have two selections, but only the first one shows on record data on report.
The second one doesn't show up, but the sql query doesn't give me any errors.
Should i put everything between ( ) ' s?

SELECT  
orcamento.descricao,
orcamento.quantidade,
orcamento.precounitario,
orcamento.preco,
orcamento.precocomiva

FROM orcamento

Where orcamento.id_orcproforma={TableGridpreorc}; 

///// ONLY THIS FIRST PART SHOWS UP ON REPORT, AFTER THIS LINE NOTHING SHOWS ON RECORD DATA
                                                 
SELECT
assistencia.data,
Assistencia.record_count,
Clientes.Nome,
Clientes.Empresa,
Clientes.Nif,
Clientes.Email,                            
Clientes.contacto1,
Assistencia.diagnosticocliente,                                                                                                                                                                                                
Assistencia.nserie,
Assistencia.taxaurg,
Assistencia.Reincidencia,
Assistencia.Autorização,                                                            
Assistencia.Backup,
Assistencia.Acessórios,
Assistencia.Danos,
Assistencia.Avaria,
Assistencia.SelodeGarantia,
tipo.tipo,
marca.marca,
modelo.modelo,

                                                                                                             
(CASE assistencia.backup  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end ),                                
(CASE assistencia.taxaurg  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end),
(CASE assistencia.autorização  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end ),
(CASE assistencia.reincidencia  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end)                              

FROM assistencia                                                                                                                                                      


LEFT OUTER JOIN clientes on assistencia.id_clientes = Clientes.id   
LEFT OUTER JOIN tipo on assistencia.id_tipo = tipo.id                              
LEFT OUTER JOIN marca on assistencia.id_marca = marca.id                               
LEFT OUTER JOIN modelo on assistencia.id_modelo = modelo.id


WHERE assistencia.id=$id


                                                                                                       
                                                        

 

Re: SQL Report ID from...

nevermind, i don't know why i didn't use join this time...

now it works:

SELECT
orcamento.descricao,
orcamento.quantidade,
orcamento.precounitario,
orcamento.preco,
orcamento.precocomiva,  
assistencia.data,
Assistencia.record_count,
Clientes.Nome,
Clientes.Empresa,
Clientes.Nif,
Clientes.Email,                            
Clientes.contacto1,
Assistencia.diagnosticocliente,                                                                                                                                                                                                
Assistencia.nserie,
Assistencia.taxaurg,                                          
Assistencia.Reincidencia,
Assistencia.Autorização,
Assistencia.Backup,
Assistencia.Acessórios,
Assistencia.Danos,
Assistencia.Avaria,
Assistencia.SelodeGarantia,
tipo.tipo,
marca.marca,
modelo.modelo,
                                                                                                             
(CASE assistencia.backup  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end ),                                
(CASE assistencia.taxaurg  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end),
(CASE assistencia.autorização  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end ),
(CASE assistencia.reincidencia  WHEN 1 THEN 'Sim' WHEN 0 THEN 'Não' ELSE '' end)                              


FROM orcproforma

LEFT OUTER JOIN orcamento on orcamento.id_orcproforma = orcproforma.id
LEFT OUTER JOIN assistencia on assistencia.id = orcproforma.id_assistencia              
LEFT OUTER JOIN clientes on assistencia.id_clientes = Clientes.id   
LEFT OUTER JOIN tipo on assistencia.id_tipo = tipo.id                              
LEFT OUTER JOIN marca on assistencia.id_marca = marca.id                               
LEFT OUTER JOIN modelo on assistencia.id_modelo = modelo.id    

WHERE orcproforma.id={TableGridpreorc}