(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Importing text into a money field
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Importing text into a money field (Read 956 times)
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Importing text into a money field
Aug 31st, 2007 at 6:06pm
Print Post Print Post  
I have one field in Q&A that's supposed to contain a money value (C.O.D. Amount), but for some reason I made it a text field when I designed the dtf.  What did I know, I was young and stupid.

What would happen if I tried to import those records into Sesame?  Would Sesame bounce the entire record, or just ignore the data and not fill the field?

If I make the target field in Sesame into a text field and then (I assume) successfully import the data, what would happen to those records which contain stuff like "$241 per hr"?
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Ben
Lantica Support
*****
Offline



Posts: 218
Joined: Apr 7th, 2005
Re: Importing text into a money field
Reply #1 - Aug 31st, 2007 at 6:27pm
Print Post Print Post  
Quote:
If I make the target field in Sesame into a text field and then (I assume) successfully import the data, what would happen to those records which contain stuff like "$241 per hr"?


A text field will store anything so the data would still display as "$241 per hr".
  
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Importing text into a money field
Reply #2 - Aug 31st, 2007 at 6:41pm
Print Post Print Post  
I'm sorry, I didn't finish my thought/question, my error.  What I meant to say was, what if I make the field into a text field in Sesame, import the data (containing numbers and text) and then change the field type to Money?
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Importing text into a money field
Reply #3 - Aug 31st, 2007 at 7:54pm
Print Post Print Post  
How about making a money field in Q&A.

Separate the numeric values from existing field and put into the new money field.  Now bring the new money field into Sesame, and leave the old field there.

Get money value by finding position of decimal in mixed field, and using @Left(MixedField,@Instr(MixedField,".")+2)

Use Mass Update to use that formula to populate the new money field.

------------------
Could do the same in Sesame if original element is bound to Text.  Do Mass Update to new Currency element , then delete the original text element.  You could also do @Right to capture the text portion and keep it in the text field for unit of measure.
« Last Edit: Aug 31st, 2007 at 9:19pm by Bob_Hansen »  


Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
WWW WWW Skype/VoIP Skype/VoIP  
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing text into a money field
Reply #4 - Aug 31st, 2007 at 9:12pm
Print Post Print Post  
Infinity wrote on Aug 31st, 2007 at 6:41pm:
I'm sorry, I didn't finish my thought/question, my error.  What I meant to say was, what if I make the field into a text field in Sesame, import the data (containing numbers and text) and then change the field type to Money?


Since the values are not actually Money, the results for any given record will be unpredictable and depend on the exact content of the field. Bottom line: Nothing good. If you want the data to be Money, you need to clean it up. Even if Sesame managed to pick 241 out of the value you show, you would then not know if that was 241 per hour, per day, per barrel, etc.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Importing text into a money field
Reply #5 - Aug 31st, 2007 at 9:21pm
Print Post Print Post  
Yep yep yep, thank you.  I'm going to change the target element in Sesame to text, and let my users worry about the formatting when they type the value in.  The specific field I'm concerned with has no math performed on it, so what the hey.
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Importing text into a money field
Reply #6 - Sep 2nd, 2007 at 8:13pm
Print Post Print Post  
I decided to give the import a go without changing my money target field to text, mainly because I had written programming that used it in mathematical operations in several places and I wanted to retain that capability.

I am greatly pleased to say that Sesame blanked the field whenever the import data contained just text such as "call shop", and stripped the text off whenever the import data contained numbers and text.  True, data that once said "$240 per hour" now just says "$240", but since it's all historical data for reference only I can live with that.

Woo Hoo!  I may actually get some sleep this weekend!   Cheesy
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge