Welcome!
This community is for professionals and enthusiasts of the Genio platform.
Share your questions and challenges, and help your partners!
Get a value depending on the enumeration
I created a table to convert between currencies which have a field called "conversão" with a enumeration to each currency:
DE = Dólar to Euro; DK = Dólar to Kwanza; ED= Euro to Dólar; EK= Euro to Kwanza; KD= Kwanza to Dólar; KE = Kwanza to Euro
Then I choose the currencies and set the exchange of the day. It's working fine until here
But what I need is in another table, get an specific value, that is:
Select separately the "valor" from each "conversão" (DE, DK,EK,etc..) so I can add in another table a calculation that will be:
This specific value (the VALUE from Dólar x Kwanza, for example) and multiply another field from another table.
And very important, it must be the LAST value added in table "cambio"
This is my exchange("CAMBIO") table. I'm trying to get the VALOR from an enumeration with value "DK":
So then I can multiply for the value I want but It's not working.
Actually I tried other ways but without success
I did exactly like you said, but still not working.
How you can see, "euro" is the main currency, and the commission is 400 but the calculation Euro to Dollar is not working
I'm not using OPREG anymore, so is OPORT the main table
Apparently it's correct:
Hi Sónia! I've tried it many ways, and this happens:
This is how i did in Functions:
"@moeda" is "E" or "D" because is how is in the enumeration. I tried with "Euro" and "Dolar" too. Is "PC0CAMB" because my table doesn't have the "I" (CAMBI) and is CONVERSE, not CONVERSA. So thats why I'm thinking is ok
My table is like this:
There's no error when generate, but this error appear when compilate:
I clicked "Yes" just to see what happens, and when I tried to to the Database Maintenance, that's the message:
I changed "PC0CAMB" to "CODCAMB" just to test, but the error persists
As an alternative to the Last value, try using the CT Lookup formula. It works the same way but doesn't need to be related.
Ok, you first need to add different fields to get all the different last values of Cambios, then you need to use arithmetic to calculate.
And, only then you can do the arithmetic formula. The formula will be something like [OPREG->COMIDOLA]*[OPREG->CAMBED]
NOTE: CAMBED is the field that you need to create with the CT formula to get the Last Value of the Cambio between Euro x Dollar.
I think i already did it Sónia.
I created "EURXDOL" which is the fild that will get the last "Euro x Dolar" cambio
Then, I created another field named "EDCALC" to do the arithmetc formula: "EUROXDOL * The real commission value(Which is COMIDOLA)"
I wrote a new solution for you in the last answer post. Try if this will work.
I don't know if I understood everything that you want.
However, it seems that you want the last value of the Cambio value, depending on the conversion type. For that, you can use the Last Value formula, see below. The last value formula can be used in Parent tables, this means that CAMBIO needs to be below OPREG to work.
_______________________________________________________________________________________________________________________
30.11.2022
In the meantime, I realized why it wasn't working. Your Cambio table had a relation with the Opportunity table but nowhere was the relation between the two tables empty, so the returned value was always 0.
What you should do is first pay the relation (FK) of opportunity in Exchange. Ideally, you should use CT Lookup but you can't add conditions to the data. So you can use a manual function.
In the OPPORT table your field DOLKWAN you can use a manual function I called "LastValueEuro()", inside the parentheses you can put the coin (your MOEDA field) and then multiply this value with the commissions' value.
In Function button add a new function.
Give the name that you want. (E.g. LastValueCambio)
Set the data type of the return value (the with should be the same that the conversion value from cambio table)
Choose the Execution type (in the example you just need execute in Database.
Enter the parameters that you want, I thing you just need the coin parameters for this function.
Insert an implementation, in the example T-SQL works.
In T-SQL you need to use the Transact SQL Language.
\https://learn.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver16
The next code solve the issue in my example, you need to change it for your reality. My suggestion: try execute the code inside the return in the SQL Server Management Studio, if it will returns that you want it will works.
If you want declare a new variable use: DECLARE myVariable VARCHAR(100)
If you want refers some parameter, use @parameter (E.g. @moeda)
SELECT TOP(1 ) VALUE FROM PC0CAMBI
WHERE CONVERSA = CASE
WHEN @moeda='Euro' THEN 'ED'
WHEN @moeda='Dolar' THEN 'DE'
END
ORDER BY DATA DESC)
Keep Informed
About the Community
Question tools
Stats
| Asked: 16/11/22, 15:20 |
| Seen: 10147 times |
| Last updated: 24/11/22, 14:49 |
The value of CAMB->CONVERS should be the code of enumeration, are you using the code of the enumeration element?