insert currency name and symbol

Question

insert currency name and symbol

lemonsqueezy101

SSC Eights!

Points: 855
More actions
December 12, 2008 at 2:58 am

#155016

Hi Guys,
I have a requirement to store the currency name and the currency symbol for a record inserted by a user. I only want to have the user select the value once and they will be entering the currency name from a DropDownList.
How would I insert the correct currency symbol into a separate field along with the currency name?
Can I write something into the INSERT query or would a trigger accomplish this?
Any ideas?
Thanks in advance
M

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

lemonsqueezy101 (12/12/2008)
Hi Guys,
I have a requirement to store the currency name and the currency symbol for a record inserted by a user. I only want to have the user select the value once and they will be entering the currency name from a DropDownList.
How would I insert the correct currency symbol into a separate field along with the currency name?
Can I write something into the INSERT query or would a trigger accomplish this?
Any ideas?
Thanks in advance
M

Why not prepare a full list beforehand? It would be a lot simpler...

http://www.xe.com/symbols.php

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lemonsqueezy101 SSC Eights! Points: 855 More actions · Answer 2

Hi Chris,

I already have the currency name and currency symbol list in a table with the Currency Name populating the drop down list the user will select from.

This DDL is inserting the currency name in the field I want into a separate table but I'm unsure how to insert the symbol in the other currency symbol field based on what is selected from the DDL.

Thanks

M

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

lemonsqueezy101 (12/12/2008)
Hi Chris,
I already have the currency name and currency symbol list in a table with the Currency Name populating the drop down list the user will select from.
This DDL is inserting the currency name in the field I want into a separate table but I'm unsure how to insert the symbol in the other currency symbol field based on what is selected from the DDL.
Thanks
M

I'm obviously missing something here - if you've got a lookup table with currency code and currency symbol, why would you want to put both into another table - when you can lookup the currency symbol? Not enough coffee - what am I missing!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lemonsqueezy101 SSC Eights! Points: 855 More actions · Answer 4

I'm sure it's me that's missing something not you!

From what I can gather, you're saying I can use the value I have that was entered via the DDL, check what the corresponding symbol is in the lookup table and insert this value where is is required???

Makes sense, but i'm not sure how to do this.

I thought maybe storing the currency name and currency symbol in the record may be easier as the select queries i'm using to display the record are becoming a bit complex (for me anyway!)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

lemonsqueezy101 (12/12/2008)
I'm sure it's me that's missing something not you!
From what I can gather, you're saying I can use the value I have that was entered via the DDL, check what the corresponding symbol is in the lookup table and insert this value where is is required???
Makes sense, but i'm not sure how to do this.

I'm still confused, M! Is this a save from the user or is it more complicated than that? If it's a save, what's the server-side mechanism?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lemonsqueezy101 SSC Eights! Points: 855 More actions · Answer 6

I'll start again. I have a table where a number of fields are inserted by a user. One of these fields is "currencyname" which stores the currency name eg. pounds, dollars, euros.

When I retrieve this data I have a requirement to display the currency name, e.g "pounds" and the currency symbol e.g. "£" as part of the same SELECT query. Currently however, I only have "pounds" stored in the record, so I thought there may be a way of inserting "£" in a new field "currencysymbol" when "pounds" is selected from the ddl on insert.

sorry for any confusion

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

in the recordset SELECTED for the user, or the server-side table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vladan SSC-Insane Points: 22072 More actions · Answer 8

I think that the main point is:

When I retrieve this data I have a requirement to display the currency name

So, as has already been mentioned, you don't need to insert the symbol anywhere. You just pull the symbol from your Currency table, based on the currency code that was stored in the row you want to display.

When someone is entering data, and selects a value from drop-down list (e.g. pounds), you save not this word which is mere description, but the code of selected currency (primary key of Currency table; it can be a meaningless identity number, or a standard code - 'GBP'). Later, when you are required to display this row somewhere, you can easily pull the necessary values of international code and name from Currency table, based on ID saved with the row.

Depending on what you're using it for, you may find it helpful to write a view, that will pull all such additional information from other tables, and refer to this view when displaying any row.

Is that understandable? Does it solve your problem? If not, why?

Garadin One Orange Chip Points: 29613 More actions · Answer 9

If your currency table is set up like this:

tblCurrency

CurrencyID

CurrencySign

CurrencyName

(Don't shoot me for repeating the tablename in every field, or for using tbl in a table name, I'm doing it to explain a point), the best value to store in your other table would be Currency ID. It's a tiny bit more work on both the inserts and the selects later, but it's good relational design. Don't sacrifice database structure to make a particular piece of coding "easier", or you'll regret it later, and it'll go from a slight inconvenience to a major obstacle.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

lemonsqueezy101 SSC Eights! Points: 855 More actions · Answer 10

Hi Guys,

Following on from Seth's post...

I understand the design of the tblCurrency and the reasons for it. I also understand that I will insert CurrencyID in the "other" table.

When I retrieve the CurrencyID field in the "other" table, how would I instruct the SELECT query to then take that value in the CurrencyID field, and return the appropriate CurrencySign AND CurrencyName from tblCurrency for display?

Garadin One Orange Chip Points: 29613 More actions · Answer 11

Easiest thing in the world!

Let's say your other table is:

TableSomething

A

B

C

CurrencyID

You'd do this:

SELECT

TS.A,

TS.B,

TS.C,

C.CurrencySign,

C.CurrencyName

FROM TableSomething TS

INNER JOIN tblCurrency C ON TS.CurrencyID = C.CurrencyID

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 12

This is actually the easy part. The harder part will come when you need to convert between the currencies. There are a lot of issues using currency types as part of a design. I'm sure I'm not even scratching the surface here, but a few things you'll want to think about are:

What will happen when one of your customers decides to switch their payment type from one currency to another.

What will happen if a customer pays with different currency types from different sites.

How are you getting updated Currency conversion information.

What will happen if a member of your staff puts in the wrong currency type initially and then goes back and changes it.

While this may lead this thread into more of a design direction than a T-SQL one, I'd imagine that the best way to handle this would be to store all product/service pricing information in one currency, and then come up with business rules for when the conversion will take place, and then store the converted information once.

For instance, say you store all product info in american dollars, but your customer wants to pay in Euros. At the time an invoice was generated, you'd look at your most recent record for Dollar > Euro conversion, calculate the amount in Euros and store those numbers with your invoice. You have to store the information at point in time, or your invoice will change constantly.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

lemonsqueezy101 SSC Eights! Points: 855 More actions · Answer 13

Aah, gotcha,

I've place the join inside my existing query and it works beautifully.

Thanks for the extra advice. I'll have to have a think about all the issues you've raised.

Many Thanks for the help

Vladan SSC-Insane Points: 22072 More actions · Answer 14

Garadin (12/12/2008)
At the time an invoice was generated, you'd look at your most recent record for Dollar > Euro conversion, calculate the amount in Euros and store those numbers with your invoice.

This is not absolutely necessary if you have currency conversion rates stored in a table together with their respective validity dates. Every invoice has a date, so you can find at any moment the correct conversion rate.

However, it is a good idea to store the exact converted amount together with the invoice. For example, you could later realize, that the conversion rate was incorrect due to input error - but the invoice is already printed and sent. If the converted amount is stored with the invoice, it is easier to write a query that will show you what invoices need "repair".