(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Merging Data based on unique data
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Merging Data based on unique data (Read 1798 times)
polishrob
Member
*
Offline



Posts: 12
Joined: Nov 7th, 2007
Merging Data based on unique data
Nov 7th, 2007 at 10:03pm
Print Post Print Post  
Greetings,
I was recently tasked with converting a Q&A database for use with Sesame. The conversion process went well. The issue I have now is trying to recreate a macro that posted payment data from one database to another. The data fields are mostly the same but it should only update the record with the proper account number (which is unique). Both databases are converted and working independently however I'm stumped as to were to begin. The stock copy functions work well but I'm lost as how to use the unique field (account #) to get it into the proper record.

I've RTFM the manual and skimmed through the programming guide but it was beyond my current understanding. Any gentle prods into the right direction would be greatly appreciated.

Thank you in advance,
Rob
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Merging Data based on unique data
Reply #1 - Nov 7th, 2007 at 10:08pm
Print Post Print Post  
This is best done using SBasic programming. In your case, probably as part of a Mass Update.

If you are using version 1.x, you want to either use use @XPost to push your information, or @XLookup/@XLookupSourceListAll to pull the data.

If you are using version 2.0, you want to use the XResultSet family of commands.

In both cases, you can use the unique value to make sure you only affect the record you want.

If you need more help, feel free to ask and one of us will make you some examples.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
polishrob
Member
*
Offline



Posts: 12
Joined: Nov 7th, 2007
Re: Merging Data based on unique data
Reply #2 - Nov 7th, 2007 at 10:16pm
Print Post Print Post  
Okay, you've got me excited now! And HOLY fast a 5 min response?

I am using the latest version 2.03. ANY help or examples you could provide would be GREATLY welcomed. I'm not afraid of getting a little dirty I just need to know where the shovel and wheelbarrow are kept.

Thank you again,
Rob
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Merging Data based on unique data
Reply #3 - Nov 8th, 2007 at 12:35am
Print Post Print Post  
OK, say you have an Accounts database with a field called Balance and a unique AccountID field.

Say you also have a Payments form with an element on it named Amount and an AccountID element whose value matches a record in Accounts.

You want to update the Balance on the correct Accounts record when you enter a Payment. You might put a Post Payment button on your Payments form with something like the code below in the On Element Entry event.

Note that on Payment, I am referring to forms and elements. For the target, Accounts, I am working with the underlying database and fields. This is important. Normally your code refers to elements on forms. XResultSet, however, works directly with the underlying fields.

GLOBAL CODE
[code]#include "sbasic_include.sbas"[/code]

PostPayment::OnElementEntry
[code]
var vRS as Int
var vBalance as Money

     // Open a result set with the correct Account record
     vRS = @XResultSetSearch(@FN, "Accounts", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!AccountID=" + @Str(AccountID))
     If vRS > -1
     {
           // Make sure there is exactly one matching record
           If @XResultSetTotal(vRS) = 1
           {
                 // Make sure we can change the record
                 If @XResultSetLocked(vRS) = 0
                 {
                       // Get the current balance
                       vBalance = @ToMoney(@XResultSetValue(vRS, "Balance"))
                       
                       // Do the math
                       vBalance = vBalance - Amount

                       // Write the new value back to the record
                       XResultSetValue(vRS, "Balance", @Str(vBalance))
                 }
                 Else
                 {
                       @MsgBox("Error: Account record is locked by another user.", "", "")
                 }
           }
           Else
           {
                 @MsgBox("Error: There must be exactly one matching Account record.", "", "")
           }
           XResultSetClose(vRS)      // Close the result set
     }
     Else
     {
           @MsgBox("Error: Cannot open XResultSet.", "", "")
     }
[/code]
« Last Edit: Nov 14th, 2007 at 7:33pm by Hammer »  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
polishrob
Member
*
Offline



Posts: 12
Joined: Nov 7th, 2007
Re: Merging Data based on unique data
Reply #4 - Nov 12th, 2007 at 7:21pm
Print Post Print Post  
Happy Monday,

I'm loving the @XResultSetSearch command. I'm able to rip through my databases now and pull the data I need. Thank you oh so much for the code sample  Wink

I guess all this playing is bringing up more questions.

Is it possible to manipulate a completely different database from within the current working one? All of the databases I'm working with are single entities with one form. They are all local and contained within the same dir. If not, should I merge all the databases together? Do I have any other options?

Also, what is the best way to delete all the data out of a database programmatically? After I copy out the data they like to start over weekly. How would you do it?

Finally, they only want to use this ability at the close of business on Friday. Should I create a new blank form and put the code there? Should I create a user selection and prompt them to continue before running the program?

Now knowing enough to get into trouble Smiley

Thanks again for all your help,
Rob
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2482
Joined: Aug 20th, 2003
Re: Merging Data based on unique data
Reply #5 - Nov 12th, 2007 at 9:32pm
Print Post Print Post  
polishrob wrote on Nov 12th, 2007 at 7:21pm:
Is it possible to manipulate a completely different database from within the current working one? All of the databases I'm working with are single entities with one form. They are all local and contained within the same dir. If not, should I merge all the databases together? Do I have any other options?


Yes that is possible. The first argument of the @XResultSetSearch command is the path and filename to the Sesame DB file that you want to use the commands against. So instead of using @FN, you would use something like "Data\MyOtherFile.db"

Quote:
Also, what is the best way to delete all the data out of a database programmatically? After I copy out the data they like to start over weekly. How would you do it?


If it is the same records that you are copying out using the XResult commands, I would use the XResultSetDeleteRecord() in a loop to delete all the records.

Quote:
Finally, they only want to use this ability at the close of business on Friday. Should I create a new blank form and put the code there? Should I create a user selection and prompt them to continue before running the program?


You can create a new blank form and put the command button on it to run this routine. You can also prompt the user telling them what they are going to be doing, and if they are sure they want to continue. How you run it is really up to you or the users.

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
polishrob
Member
*
Offline



Posts: 12
Joined: Nov 7th, 2007
Re: Merging Data based on unique data
Reply #6 - Nov 12th, 2007 at 11:14pm
Print Post Print Post  
Thanks for the info Reaper.

I feel as though I'm nearing the end and learning a lot.

I created a button on a new form that runs my query. Thanks

The following code sample loops through costs.db and pulls out the values I requested. For the time being I have them written to a window (for my protection). How would I go about posting those values to another database with the correct values based on the unique account number (Acct no)? For sake of simplicity, let's say all field names match between databases. I'm guessing the command to use should be XResultSetValue and it should go right before the next statement (to keep it in the loop) but how do I know I'm updating the correct record (based on matching account names) in the other database? Do I add a line for each updated value for (client, name, service fee, etc)? Also, is there a way to do a mock run without actually doing anything?

I hope all that made sense.
Obviously this will not work but there wasn't any detailed usage samples in the help file.
XResultSetValue("data\ron\qamain.db" vRS, "qamailbal", @Str(vqamailbal))

As always you guys are great with your replies.
Thank you again,
Rob

[code]
#include "sbasic_include.sbas"

var vRSHandle as int
var vNoOfRecords as int
var vLoop as int
var vAccountNo as string
var vClient as string
var VName as string
var VServiceFee as string
var Vqamainbal

     vRSHandle = @XResultSetSearch("data\ron\costs.db", "Costa", SEARCH_MODE_OR, SEARCH_SYNTAX_QA, "")
     if(vRSHandle > -1)
     {
           vNoOfRecords = @XResultSetTotal(vRSHandle)
           for vLoop = 1 to vNoOfRecords
                 XResultSetCurrentPosition(vRSHandle, vLoop)
                 vAccountNo = @XResultSetValue(vRSHandle, "Acct no")
                 vClient = @XResultSetValue(vRSHandle, "Client")
                 vName = @XResultSetValue(vRSHandle, "Name")
                 vServiceFee = @XResultSetValue(vRSHandle, "Service Fee")
                 vqamainbal = @XResultSetValue(vRSHandle, "QAmain bal after adjustment")
                 WriteLn(@Str(vLoop) + " Of " + @Str(vNoOfRecords) + "      Account: " + vAccountNo + " cleint: " + vclient  +" Name: " + vname + " Service Fee " + vServiceFee)
                 

           next
           XResultSetClose(vRSHandle)
     }


[/code]
  
Back to top
 
IP Logged
 
polishrob
Member
*
Offline



Posts: 12
Joined: Nov 7th, 2007
Re: Merging Data based on unique data
Reply #7 - Nov 14th, 2007 at 5:18pm
Print Post Print Post  
Oh no, I hope I didn't wear out my welcome!  Cry

Well, regardless you've helped me out tremendously. I have been looking at the manuals and samples and either I don't have enough experience relating what other have done to what I'm trying to do or I'm missing something completely. I would rather not call in the big guns (hired help) as 1.) I am having fun. 2.) I feel as though I'm close to a breakthrough. (As you all cringe at the thought of my programming proficiencies  Smiley

Any comments, suggestions, or criticisms are welcomed.

Thank you,
Rob
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Merging Data based on unique data
Reply #8 - Nov 14th, 2007 at 5:19pm
Print Post Print Post  
I'm getting there! Sorry I didn't get to this yesterday. I meant to.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Merging Data based on unique data
Reply #9 - Nov 14th, 2007 at 7:37pm
Print Post Print Post  
Rob: It's hard to test without your environment, but this should get you going. I've adapted the original example I gave you into a Function which I am calling inside your for loop. Let me know if you need clarification on how this works.

I also added a type to your declaration of Vqamainbal.

Code
Select All
#include "sbasic_include.sbas"

FUNCTION PostToAccount(vMatch as String, vAmount as Money) as Int
var vRet as Int
var vRS as Int

	vRet = 0
	// Open a result set with the correct Account record
	vRS = @XResultSetSearch("data\ron\qamain.db", "QAMain", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!AccountID=" + vMatch)
	If vRS > -1
	{
		// Make sure there is exactly one matching record
		If @XResultSetTotal(vRS) = 1
		{
			// Make sure we can change the record
			If @XResultSetLocked(vRS) = 0
			{
				// Update the account record
				XResultSetValue(vRS, "QAmain bal after adjustment", @Str(vAmount))
			}
			Else
			{
				vRet = 3
			}
		}
		Else
		{
			vRet = 2
		}
		XResultSetClose(vRS)	// Close the result set
	}
	Else
	{
		vRet = 1
	}
	Return vRet

END FUNCTION

var vRSHandle as int
var vNoOfRecords as int
var vLoop as int
var vAccountNo as string
var vClient as string
var VName as string
var VServiceFee as string
var Vqamainbal as Money
var vFlag as Int

	vRSHandle = @XResultSetSearch("data\ron\costs.db", "Costa", SEARCH_MODE_OR, SEARCH_SYNTAX_QA, "")
	if(vRSHandle > -1)
	{
		vNoOfRecords = @XResultSetTotal(vRSHandle)
		for vLoop = 1 to vNoOfRecords
			XResultSetCurrentPosition(vRSHandle, vLoop)
			vAccountNo = @XResultSetValue(vRSHandle, "Acct no")
			vClient = @XResultSetValue(vRSHandle, "Client")
			vName = @XResultSetValue(vRSHandle, "Name")
			vServiceFee = @XResultSetValue(vRSHandle, "Service Fee")
			vqamainbal = @XResultSetValue(vRSHandle, "QAmain bal after adjustment")
			WriteLn(@Str(vLoop) + " Of " + @Str(vNoOfRecords) + "	Account: " + vAccountNo + " cleint: " + vclient  +" Name: " + vname + " Service Fee " + vServiceFee)

			vFlag = PostToAccount(vAccountNo, vqamainbal)
			If vFlag <> 0
			{
				WriteLn("Error posting to account: " + vAccountNo, "Error Code: " + @Str(vFlag), "")
			}

		next
		XResultSetClose(vRSHandle)
	}
  
- Hammer
The plural of anecdote is not data.
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