help needed for exchange rate query for paypal

[pascale]

I have been using cacl to do the accounts for my business for several years, i am now moving to Base 5.1.
I have a table for paypal transactions and I need to be able to calculate the exchange rate between different currencies.
here is the table

Date | Heure | Type | Devise |Avant commission

28/07/17|10.03 |conversion | USD | -536.25
28/07/17|10.03 |conversion | EUR | 410.25

29/07/17|21.45 |payment | USD | 75.99

30/0/7/17|08.00|Bank trans| EUR | -400.00

And so on, there can be up to 100 lines, and I would need to filter out all but “conversion”.

The columns date, Heure, Type, Dévise, and Avant commission are needed for the query, on one line is the amount to be exchanged and its currency(devise) and the next line is the amount it equals in the home currency, I then divide one by the other to get the exchange rate(in calc I use a formula in the table)=IF($I1155=“USD”,IF(OR(G1155=“Conversion de devise initiée par l’utilisateur”),$L1156/$L1155*-1,$T1156+0),T1156+0)
this formula gives me the exchange rate for USD against EUR. I have the same for GBP, I could use an external calc sheet but would prefer to keep all in one place.
All ideas are welcome
Thanks Paul

I have added several comments to help a bit more! I want to add a screen shoot but I need 3 points!

[Ratslinger]

Attached is a simple Base example with a single form. This form uses a table filter and a query to generate required calculation. Just select the conversion items in the drop down lists (generated from ‘ExchangeRates’ table) then select the button to get result.

Sample: ExchangeRate.odb

[pascale]

Thank you so much for your fantastic answer and your hard work, it works brilliantly. is there a way that the form can show the values and the answer is saved in the original table? a bit cheeky I know But that is probably because I didn’t explain all in my original question!!!
Many thanks again for your hard work and rapid reply
Regards Paul

[Ratslinger]

Your comment and question mod raise many more situations than answers. It seems more thought as to the DB structure is needed by yourself. For example, the number of records (not lines as in calc) can well exceed your stated 100 and you cannot assume that one record is following another. In a database, you typically don’t store results of a calculation where the information to create the result is already stored. Regardless, where would it be stored? Is it USD to EUR or EUR to USD?

[Ratslinger]

Also, with the added examples it seems transactions are mixed in with conversion factors. The sample provided no longer works since there are multiple ‘USD’ records. These and many other questions arise when now looking at the changes added. When setting up a database, the planning of the entire process is very important. Doing this first instead piecing situations as going along eliminates many problems later.

[pascale]

Thank you again for getting back to me, You are right of course, I would like to add some images and a copy of my db but not to sure how? tried adding these in the original post. I understand that much of this information needs to be found with a query,but when I saw your form I wondered if this could be used to add the result to the original table (in a new column). again Thank you for your help regards Paul

[Ratslinger]

Your post is a catch 22. You can add images & copy of DB if you have enough Karma. I could give you enough if your posting wasn’t a Wiki post (don’t use this type of posting). You also earn Karma for accepting or receiving a marked question as correct. You could also use some type of outside party to save such and reference link in question.

[pascale]

Being new to this( and many other things) I think it best to end this question and start again with a bit more info (and not wiki) so I can add images and the db itself. Once again I thank you for your kind help
Regards Paul