|
Posted by convoluted on February 21, 2008, 8:31 pm
Please log in for more thread options Glad I could help...but you're too kind with your words...:-)
The SQL I know is self taught, here's what I did - I started out with free
SQL tutorials online - hey, they're free and will get you started with the
basics (select from where group by order by) - (google "sql tutorials" and
you'll get started in no time) - - then I bought "Sam's teach yourself SQL
in ten minutes" at amazon.com it cost about 15 dollars but it took me to the
next level understanding views, triggers and transactions; then I bought
"Mastering SQL Server 2005 Express" also at amazon to get some more detail on
the db engine and using Management Studio Express, but frankly, I've really
learned SQL by trial and error on a test database on a laptop; I think I had
to figure out and re-write the view creation like ten times cause I wasn't
getting the "case" syntax right - those darn fangled parentheses...:-) - if
you can, download SQL books online as its also a very good reference - SQL
has been a lot of fun and I look forward to learning more - by the way I've
also learned some SQL on this forum from guys like Glenn, Terrible Tom, Akber
and others...
"Tara" wrote:
> Hi! That worked perfectly! Thank you so much! That's pretty much what I
> envisioned would need doing but I didn't have a clue where to start as I've
> only got the basics of SQL myself. Any tips on how I can get to the level
> that you're at? A good beginner's guide would be great if you could recommend
> something!
>
> You saved my bacon- thanks again!
>
> T.
>
> "convoluted" wrote:
>
> > Hi Tara - sorry for the delay on a followup...
> >
> > I've only been working with RMS for about a year and a half but never had a
> > chance to work with foreign currencies so I had to kick the tires a little
> > bit...having to learn RMS also meant learning SQL on my spare time...so
> > here's my two cents.
> >
> > It appears that when you set up a supplier with a foreign currency, the
> > supplier tab will add the "local cost" field but you're right, its a
> > calculated field, - I too was unable to find either a field in a table or a
> > separate table from supplierlist where this is tracked, so I think RMS
> > performs an internal calculation to come up with "local cost" - here's what
I
> > did to test your scenario on a test db
> >
> > First create a view that will track supplier info and conversion rate, your
> > syntax is
> >
> > create view supplierconversion as
> > SELECT Supplier.ID AS Supplier_ID, Supplier.SupplierName, (case
> > supplier.currencyid when 0 then '1' else (1/Currency.ExchangeRate) END) as
> > Conversion
> > FROM Supplier
> > LEFT OUTER JOIN Currency ON Supplier.CurrencyID = Currency.ID
> >
> > A view is basically a way to show data from one or multiple tables; run the
> > following after you've created the view to see the "contents" of your view
> > select * from supplierconversion
> >
> > You will see ALL your suppliers, where those that work with the native
> > currency will have a "1" in the conversion field, whereas those that work
> > with US dollars or another foreign currency will display the conversion rate
> > (the multiplier you will use to convert from foreign to local currency)
> >
> > Before you run your update item query, remember to backup the database and
> > do this after hours- also, try this on a test database before applying to
> > your production (live) database -
> >
> > Your update query is
> > update item
> > set item.cost = (supplierlist.cost * supplierconversion.conversion)
> > from item
> > inner join supplierlist on item.id = supplierlist.itemid
> > inner join supplierconversion on supplierlist.supplierid =
> > supplierconversion.supplier_id
> >
> > For those suppliers where the conversion is 1 to 1 (local currency) then the
> > supplierlist.cost will be copied into item.cost; for those suppliers where
> > the conversion is not 1 to 1, then the supplierlist.cost will be multiplied
> > by the conversion to calculate the local cost which will then be inserted in
> > item.cost
> >
> > Don't forget the "disclaimers", you're doing major mass updates to the db -
> > so test it first then use on your live db - Hope this helps......
> >
> > "Tara" wrote:
> >
> > > Hi, I don't know if I'm overlooking something totally obvious but I seem
to
> > > have encountered a serious flaw in the software. Maybe everyone is
familiar
> > > with this already but I'd really appreciate some help.
> > >
> > > Here's the scenario: RMS 2.0 HQ and Store Ops. Local currency is Euro
while
> > > a number of suppliers are set to use Dollars and Sterling. Exchange rates
> > > are updated once a week. Within Item Properties, the Supplier tab has two
> > > cost columns listing both the supplier's cost is the supplier's currency
and
> > > a local cost which is the conversion to Euro eg. Supplier Cost = $8.77
Local
> > > Cost= €6.05
> > >
> > > Here's the problem: The Cost price on the General tab is simply pulling
the
> > > figure from supplier cost column and sticking the local currency symbol in
> > > front of it. So on the General tab, I'm seeing a cost price of €8.77.
Which
> > > means the Profit Margin is wrong. And not only here, but in Reports as
well
> > > as I believe this is the field Cost price is pulled from. It's also
causing a
> > > problem in the creation of PO's.
> > >
> > > Can anyone shed any light on this?
> > >
> > > I've been working on a SQL statement to copy idata from the Supplier Cost
> > > field into the Cost field on the General tab for a number of items where
the
> > > cost price is missing, but I realise now it's actually the Local Cost that
> > > I'll need. I can't find this column in any of the obvious tables in the
> > > database. I could nearly live with the above problem if I could write a
> > > report that pulls data from the correct column.
> > >
> > > I really need help! Thanks.
> > >
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap