Normal Topic Mass Update to parse information (Read 1167 times)
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Mass Update to parse information
Sep 22nd, 2005 at 8:44pm
Print Post Print Post  
Hi -

I have a "remarks" field on my form that contains information in the following format:

Category:  ACCOUNTING & TAX SERVICES
  First:  Lawrence A.
  Last:  Fischer
  Business:  LAWRENCE A. FISCHER, CPA, PA
  Description:  Tax Preparation & Accounting

I also have a field named "Category." 

I would like to do a mass update to populate the category field with the name of the category only (in this case "Accounting & Tax Services."

There are of course many different categories of varying length but the next word "First" always starts on the next line.  Could I get some help with the mass update programming please?

Thanks!
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Mass Update to parse information
Reply #1 - Sep 23rd, 2005 at 12:20pm
Print Post Print Post  
Is Category always the first line?
Is Category always the only thing on the Category line?
Does the Category line always exist?
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Mass Update to parse information
Reply #2 - Sep 23rd, 2005 at 1:15pm
Print Post Print Post  
Yes  Smiley
Yes  Smiley
Yes  Smiley  ....

which is why I thought it should be fairly simple -- but my brain must have "stalled."  lol

I was in a hurry, so I used a macro to do most of it for me; however, I have an almost identical situation, so a more sophisticated solution would still be very much appreciated.

Thanks!
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Mass Update to parse information
Reply #3 - Sep 23rd, 2005 at 1:52pm
Print Post Print Post  
Try something like this:
Code
Select All
var vPos as Int
var vStr as String

	vPos = @InStr(Remarks, @NewLine())
	If vPos = 0
	{
		vPos = @Len(Remarks)
	}
	// Number of characters to get.
	// Length of first line - "Category:  " - 1 for the NewLine
	vPos = vPos - 12
	vStr = @Mid(Remarks, 12, vPos)
	Category = vStr
 

  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Mass Update to parse information
Reply #4 - Sep 23rd, 2005 at 2:12pm
Print Post Print Post  
Erika -

I haven't yet tried your suggestion but I decided I MUST make an effort to figure this out myself.

A significant difference between us is that you first think of S-Basic, and I (since I haven't yet been trained in S-Basic) think of the old Q&A programming that I was so proficient in.

So, same exact scenario, except for e-mail addresses where the first line will always start with Email Address:  and it doesn't really matter whether anything follows it (If there is no information after E-mail: then the e-mail address should just be left blank), except that additional information, if any, will always start on a new line.

I didn't know about the @newline() command until reading your response and that was my biggest stumbling block.  

So, here is what I had come up with for code (after learning about the @newline() command.

Code
Select All
EMail Address = @mid(Remarks,16,@len(@instr(Remarks,@newline()))) 



It seems to me that this should work; however, all I get is the first letter of the e-mail address.  Can you see something in this particular statement that I am over-looking?

Thanks!

  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Mass Update to parse information
Reply #5 - Sep 23rd, 2005 at 2:52pm
Print Post Print Post  
Erika -

I just want you to know that while I was waiting to hear about my "kindergarten" (old Q&A style)  type "programming" lol - I did try your S-Basic recommendation since it was so easy to follow.

Here is how I modified it for my e-mail scenario ..

[code]var vPos as Int
var vStr as String

vPos = @InStr(Remarks, @NewLine())

vPos = vPos - 16
vStr = @Mid(Remarks, 16, vPos)
EMail Address = vStr

Remarks = @mid(Remarks,VPos + 16,5000)code]

... and it worked like a charm.

I ommitted the code related to "If VPos = 0" because I KNOW for sure there will always be a line break.  Also, I was afraid - just in case I was wrong - and if there was no e-mail address, I might get extraneous information in the e-mail address field.

I added the last line of code to remove the e-mail address from the remarks field ... since it was now where it belonged.  The length of "5,000" was simply a "safe" number I used, knowing that the length of the remainder of the field would never exceed that amount.

I would still like to learn what was wrong with my programming; however, I now have my solution.  THANK YOU!!!

I just can't wait till you get to Tampa, for the training.

  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Mass Update to parse information
Reply #6 - Sep 23rd, 2005 at 3:28pm
Print Post Print Post  
Quote:
I would still like to learn what was wrong with my programming; however, I now have my solution.  THANK YOU!!!


Your problem is not new to SBasic. You would have had the same problem is Q&A. Here is why it's only returning one character:

Your code looks like this:
EMail Address = @mid(Remarks,16,@len(@instr(Remarks,@newline())))

The last argument to @Mid is how many characters to return. @Instr returns a number. So, if the first newline appears at position 36, @Instr returns 36.

You then ask for the length (@Len) of 36, which is 2.

Therefore, it would return 2 characters.


  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Mass Update to parse information
Reply #7 - Sep 23rd, 2005 at 3:55pm
Print Post Print Post  
Erika -

I hope you didn't misunderstand my comment about Q&A "style" programming vs S-Basic.

I fully realized that my problem was with the code I was using and had nothing to do with S-Basic.  (After all, it has probably been a decade or so, since I did programming in Q&A).

Your explanation, however, was crystal clear and I was then able to modify my original code to get it to work.  Here is what I used.

Code
Select All
EMail Address = @mid(Remarks,18,@instr(Remarks,@newline())-18)

Remarks = @mid(Remarks,@len(EMail Address) +18,5000) 



In any event, the S-Basic programming you suggested is certainly more sophisticated, just as easy (once you know it) and I feel is probably a lot more reliable.

So, again, thank you so much for all your feedback.  I can now choose which method to use and ... bottom line .... the task I had facing me has now been completed.
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Mass Update to parse information
Reply #8 - Sep 23rd, 2005 at 5:19pm
Print Post Print Post  
Quote:
Erika -

I hope you didn't misunderstand my comment about Q&A "style" programming vs S-Basic.


No. I simply mean that the SBasic code to do this is very similar to what you would do in Q&A. In Q&A, you would have used @InStr to find "
" instead of @NewLine and you would have used a field instead of a variable, but the logic is the same. Just because you are using Sesame, it doesn't mean that the programming skills you learned in Q&A are no longer useful.
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged