Lantica User Forum - Changing field Type from Number to Text

(UTC)

Lantica User Forum › Lantica Discussions › Lantica Discussion › Changing field Type from Number to Text

(Moderator: Forum Admin)

‹ Previous Topic | Next Topic ›

Page Index Toggle

Pages: 1

Send Topic

Print

Hot Topic (More than 10 Replies)

Changing field Type from Number to Text (Read 9051 times)

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Changing field Type from Number to Text Feb 21^st, 2018 at 7:30pm	Print Post
	Have a database with about 40,000 records. Customer now wants to modify relational field to include letters as Prefix/Suffix to existing numbers. Need to change the exixting Number field (key relational field) into a Text field. Current number format is all digits, no commas, no decimals, no $. Display is a whole number like 33592. Need to change to look something like PD33592skp. Result shows all numbers with 8 decimals, like 33592.00000000. How to make it convert to no decimals, true text format? Custom format only has options to control cases. Database is too complex to do MUD to strip off last 9 chars, may take about 8 hours to process all 40000 records.


	IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Changing field Type from Number to Text Reply #1 - Feb 21^st, 2018 at 7:34pm	Print Post
	You'll need to use @Decimals() in a Mass Update to strip the 0's. Converting from a number to a string means the entire numerical value is converted. -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Changing field Type from Number to Text Reply #2 - Feb 21^st, 2018 at 7:52pm	Print Post
	Need another answer. As noted in original msg, Mass Update (MUD) is really a last choice, need something else. OR, is there a way I can disable all other programming from running with a MUD. Especially OnFormEntry/ or OnElement/Form Exit/Change ? It is the internal code that causes the long time on the MUD operations.


	IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Changing field Type from Number to Text Reply #3 - Feb 21^st, 2018 at 8:01pm	Print Post
	Use Mass Update on Engine. -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: Changing field Type from Number to Text Reply #4 - Feb 21^st, 2018 at 8:01pm	Print Post
	obfusc88 wrote on Feb 21^st, 2018 at 7:52pm: OR, is there a way I can disable all other programming from running with a MUD. Never tried this, but can you surround existing code with something like: Code // Insert next line to prevent running during MUD operations // Mode(2) is Search Mode, should not run during MUD If @Mode(2) Then { ...Existing Code ...More code ...End of code } Insert this code in all other areas that run. And then do the MUD on the field with the decimals problem? When MUD is done, comment out the top and bottom lines to keep available for future MUD operations. Maybe a future enhancement to have an @Mode(MUD) value?

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Changing field Type from Number to Text Reply #5 - Feb 21^st, 2018 at 8:30pm	Print Post
	There is no need for an @Mode() flag because no form events run when a Mass Update is running. -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Changing field Type from Number to Text Reply #6 - Feb 21^st, 2018 at 9:56pm	Print Post
	Ray - What do you mean by "use Mass Update on Engine"? Do you mean on a stand alone PC, or on the Server vs. a work station? Something else? I thought some code would run during Mass Update, even if just to make fields visible, change fonts, etc? If no code is running, then why would a simple Mass Update take such a long time to process. For example just doing the ParentOrder = @Decimals(ParentOrder,0) routine should be a simple routine, but earlier "simple" Mass Updates on this database have run longer than a work shift. It might take 12 hours on a workstation, so I would copy the data base from my customer to my office, and do the Mass Update on a stand alone, and it would still take 6-7 hours. Since this Mass Update I need to do for the decimals would be on a natural relation key field, I guess that it will also involve indexing and matching to sub forms that will add more time? Some older records, have no subform records, would that group run faster since no linking is necessary? So, maybe doing two batches might speed it up?


	IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Changing field Type from Number to Text Reply #7 - Feb 22^nd, 2018 at 3:51pm	Print Post
	obfusc88 wrote on Feb 21^st, 2018 at 9:56pm: Ray - What do you mean by "use Mass Update on Engine"? Do you mean on a stand alone PC, or on the Server vs. a work station? Something else? I mean Mass Update on Engine. It was introduced in version 2.0.5 back in 2008. Page 15-16 of the Errata http://www.lantica.com/Files/errata.pdf Quote: I thought some code would run during Mass Update, even if just to make fields visible, change fonts, etc? If no code is running, then why would a simple Mass Update take such a long time to process. No, I just verified this with 2.6.4. Even the universal event(Which pretty much always runs and should never be used unless you are building a game) doesn't run when a Mass Update is running. It's slow because you are pulling every parent record and it's child records from the Server to the Client machine. The forms still format and display the data. Then the update happens. Then the Parent and it's Child records are sent back to the server, and the next one is passed over. Mass Update on the Engine eliminates a lot of this as it runs on the Sesame server working directly on the database itself which is why you have to use Field names and not Element names when using it. This eliminates having to send every record(and the child records) back and forth from the Server to the Client. So it eliminates the network, child records, element formatting, drawing the record on screen, and probably something else that I can't recall at the moment, so it is much much much faster. -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Changing field Type from Number to Text Reply #8 - Feb 22^nd, 2018 at 4:41pm	Print Post
	Thanks, Ray. I totally missed this. Don't recall ever hearing about this. Will check it out today.


	IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Changing field Type from Number to Text Reply #9 - Feb 27^th, 2018 at 6:31pm	Print Post
	As I have mentioned in the past, Ray .... You are the GOAT! That has saved me so much time. I cannot believe this has been here for so long, and I never used it. It is now my GOTO tool! This should be MUST READING for anyone doing Mass Updates.


	IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Changing field Type from Number to Text Reply #10 - Feb 27^th, 2018 at 6:48pm	Print Post
	obfusc88 wrote on Feb 27^th, 2018 at 6:31pm: As I have mentioned in the past, Ray .... You are the GOAT! Ummm MEEEEEHH MEEEEEH. I think I said that right... Google Translate won't convert "Thank You" to Goatish and my Goatish is a bit rusty as it's been a while since I've hung out over in the Goat barn. I am pretty fluent in Horse-anese however! Quote: This should be MUST READING for anyone doing Mass Updates. It has it limitations as discussed in the documentation, so it won't replace client side Mass Updates, but for simple mass updates, it's a fantastic tool. -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Changing field Type from Number to Text Reply #11 - Feb 27^th, 2018 at 7:12pm	Print Post
	Ray the Reaper wrote on Feb 27^th, 2018 at 6:48pm: obfusc88 wrote on Feb 27^th, 2018 at 6:31pm: As I have mentioned in the past, Ray .... You are the GOAT! Ummm MEEEEEHH MEEEEEH. I think I said that right... Google Translate won't convert "Thank You" to Goatish and my Goatish is a bit rusty as it's been a while since I've hung out over in the Goat barn. I am pretty fluent in Horse-anese however! Quote: This should be MUST READING for anyone doing Mass Updates. It has it limitations as discussed in the documentation, so it won't replace client side Mass Updates, but for simple mass updates, it's a fantastic tool. -Ray Yes, but this is a good reason to make Element Names the same as the Field Names. Then you can use MUD on Engine with no extra issues. I had run into a few that were different, so I changed the Field Names to match the Element Names, so no need to go through code to fix Element Names that don't change (maybe in quotes, referenced on other forms, etc.) from the "normal" change process.


	IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Changing field Type from Number to Text Reply #12 - Feb 27^th, 2018 at 7:56pm	Print Post
	obfusc88 wrote on Feb 27^th, 2018 at 7:12pm: so no need to go through code to fix Element Names that don't change (maybe in quotes, referenced on other forms, etc.) from the "normal" change process. No but if you reference those fields in the XResultSet() family of commands you will still need to update the programming. -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

Page Index Toggle

Pages: 1

Send Topic

Print

‹ Previous Topic | Next Topic ›

« Board Index ‹ Board Top

Top

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

Valid XHTML

Valid CSS

Powered by Perl

Source Forge