Problems related variable sum

[Gonçalo Casanova]

Hello friends
This is my first post, and I've seen that in this forum there is content and people with true knowledge.
I will try to post my questions and if you are doing something wrong, or posting on the wrong page, peopl anticipated excuses. But let's go

I have a question execSQL and wonder if I can help.
As shown below, they have a ratio of three files:

Plan Contas_Lançamento: file with chart of accounts Description: This would be the main information that is necessary to make the sum of what is credit and some also what is debt

Released: These are the account details

Press Values: Where are the recorded values.

My problem is that I try to create separate totals:

total_credito = ExecuteSQL ("SELECT SUM (Article Item Lancto) where values Editions Contas_Lançamento Plan Receita_Despesa :: =" "" "" "Credit")

total_debito = ExecuteSQL ("SELECT SUM (Article Item Lancto) where values Editions Contas_Lançamento Plan Receita_Despesa :: =" "" "" "debt")

Does anyone have any idea what I'm doing wrong because I can not do this function sum, the only thing that appears is "?"

Thank you for your help

[eos]

1. There is no FROM; you must indicate the table you want to query.

2. You cannot use field names with blanks; but you can quote them to have them accepted.

3. If you add an optional argument, you should use it; reference it with a ?

In short, try

ExecuteSQL (" 
  SELECT SUM ( \"Article Item Lancto\" ) 
  FROM \"Lancamentos Valores\"
  WHERE \"Editions Contas_Lançamento Plan Receita_Despesa\" = ? 
  " ; "" ; "" ; "Credit" 
)

Not sure what your actual field names are, or about the name of the table you want to query; so modify that if necessary.

I suggest you read an introduction into general SQL, and its use within FileMaker; e.g. google "filemaker hacks sql" 

[Gonçalo Casanova]

Hi Eos

 

Thanks for attention

 

but I must still be doing something wrong. Does not work. Have you any idea what I still try?

Edited September 19, 2015 by Gonçalo Casanova

[bruceR]

Note that there are a couple of reasons for the question mark to show up in a field like that. One is that - the calculation is working correctly - but the size of the field is too narrow to display the result.

What happens if you click into the field? Do you still see the question mark? Or do you see a result, something like this:

12355.0010340342342342342

[Gonçalo Casanova]

Hello BruceR

I increased the size of the hood but keeps popping the question mark

Edited September 20, 2015 by Gonçalo Casanova

[bruceR]

What happens when you click into the field?

[Gonçalo Casanova]

Nothing happens. Still the question mark.

I removed the spaces of fields, redid the sentence but not resolved. Do you think the fact that he was seeking the information to sum in another table (Plano_Contas_Lançamento :: Receita_Despes = "Credit") he can not make the sum?

 

Edited September 20, 2015 by Gonçalo Casanova

[eos]

Nothing happens. Still the question mark.

I removed the spaces of fields, redid the sentence but not resolved. Do you think the fact that he was seeking the information to sum in another table (Plano_Contas_Lançamento :: Receita_Despes = "Credit") he can not make the sum?

No, the question mark is (at least) due to the fact that you're using FileMaker syntax – table::field – rather than SQL syntax – table.field. But (most certainly) also because ç is regarded as a special character, and you must quote such names.

But even that won't give you a result, because SQL doesn't use any existing FM relationships; so while a Find in a layout of Lançamentos_Valores for records where a related field has a certain value works fine (because of the correct context), it does not in SQL (which has no context); you need either to create a JOIN (recreate the relationship), or, since that field is related, query the foreign key that relates the tables – e.g.

ExecuteSQL ( "
  SELECT SUM ( Valor_Item_Lancto )
  FROM \"Lançamentos_Valores\"
  WHERE id_despesa = ?
  "; "" ; "" ; id of the foreign key that relates to a record with Crédito or Débito, respectively
)   

Note that you don't need fully qualified field names if you're only querying a single table.

With a JOIN, it could look like this:

ExecuteSQL ( " 
  SELECT SUM ( Valor_Item_Lancto ) 
  FROM \"Lançamentos_Valores\" L
  JOIN \"Piano_Contas_Lançamento\" P ON P.id = L.id_despesa
  WHERE P.receita_despesa = ? 
  "; "" ; "" ; "Crédito" 
) 

Note the dot-syntax in the fully qualified names.

PS: Please post your code as text, not as screenshots, so it easier for us to present modified examples.

PPS: You should really get some reading material on SQL and ExecuteSQL and try some simple example first; there are many things to watch out for, peculiarities and potential pitfalls (special character, reserved words and much more) that we cannot explain all within the scope of a forum post.

[Gonçalo Casanova]

Hello my friend, sorry for so many questions but unfortunately my lack of knowledge submente me to ask more questions because I still do not get run. I did not understand what to do with the JOIN. Put back the syntax with changes in text for ease. Thank you once again.

ExecuteSQL("SELECT SUM(ValorItemLancto) FROM \"LancValores\"  WHERE Plano_Contas_Lancamento::Receita_Despesa=?";"";"";"Credito")

ExecuteSQL("SELECT SUM( ValorItemLancto) FROM LancValores  WHERE Plano_Contas_Lancamento::Receita_Despesa=?";"";"";"Credito")

ExecuteSQL ( "SELECT SUM ( ValorItemLancto ) FROM \"LancValores\" 
  JOIN \"Plano_Contas_Lancamento\"  
  WHERE  Plano_Contas_Lancamento::Receita_Despesa = ? "; "" ; "" ; "Credito" )

I don't understand this syntaxe:

ExecuteSQL ( " 
  SELECT SUM ( Valor_Item_Lancto ) 
  FROM \"Lançamentos_Valores\" L
  JOIN \"Piano_Contas_Lançamento\" P ON P.id = L.id_despesa
  WHERE P.receita_despesa = ? 
  "; "" ; "" ; "Crédito" 
) 

 

I tried both ways but it did not work any

Already removed the spaces, special characters, but neither worked.

I put the report with the names of tables in the headers to see where the record comes

 

Edited September 21, 2015 by Gonçalo Casanova