(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Gathering Data from Other Files
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Hot Topic (More than 10 Replies) Gathering Data from Other Files (Read 7143 times)
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Gathering Data from Other Files
Jun 28th, 2011 at 9:20pm
Print Post Print Post  
I have a file that calculates the labor and material value for my companys' inventory.  In order to do the math, I need to get specific information from three other files within the same database.

I do this by using statements such as the following:

COMPONENT 5=@XLOOKUP(@FN,DCNUMBER,"NEWPROCS!DIACOM P\N","COMPONENT5")

Everything works correctly, but the process takes a very long time.  For my latest inventory, there are 345 items to be updated, and it takes about 25-35 minutes.

To get all the data, I use 37 @XLOOKUP statements.

I was wondering if there is a faster method to get the infomation.

What do you think?

Thanks for your help!
  
Back to top
 
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: Gathering Data from Other Files
Reply #1 - Jun 28th, 2011 at 10:02pm
Print Post Print Post  
First of all, you are in the wrong section. I am sure there are many ways you can cut down on the time. XResultset ( ) command will do that also. But what I think immediately to reduce the number of @xlookup ( ).  How many forms you are drawing value from? That number of xlookup ( ) will be enough to get the job done using following:

var returnVal as String

     returnVal = @XLookupSourceList(@Fn, IDNo, "Inventory!IDNo", "Last Name;First Name;City") //please note the Layout (form) name

You get the value in the form of a string. You can extract the value locally using following:

vLastName = @AccessStringArray (returnVal, 1)
vIDNo = @AccessStringArray (returnVal, 2)
vFirstName = @AccessStringArray (returnVal, 3)


If you use XResultSet () then you have to see from how many databases you are drawing value from and that many xResultSet ( ) command routine are enough to get all those values and work with them.

This will limit the number of times you are opening and closing the databases and hence reduce the time significantly.

Am I making sense?
« Last Edit: Jun 29th, 2011 at 1:18pm by Bharat_Naik »  
Back to top
 
IP Logged
 
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Re: Gathering Data from Other Files
Reply #2 - Jun 29th, 2011 at 1:00pm
Print Post Print Post  
Sorry about being in the wrong section.  I'm moving over to the discussion area.

Edited:
I got it. - Hammer
  
Back to top
 
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: Gathering Data from Other Files
Reply #3 - Jun 29th, 2011 at 1:27pm
Print Post Print Post  
The major difference between xlookup( ) and xResultSet (), the former work through layout or form and element while the later work directly with database and fields. I prefer xResultset () commands over xlookup (). With xResultset you can use multiple criteria to narrow down the search and work with the Resultset after getting multiple records.
  
Back to top
 
IP Logged
 
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Re: Gathering Data from Other Files
Reply #4 - Jun 29th, 2011 at 1:33pm
Print Post Print Post  
I used the suggestion to use the @XLookupSourceList command and this helped.  This works well when all the data are based on one element in my file. 

Is there an easy way to gather a series of data from a file (called MatCost) that are based on different elements in my source file?

For example, the MatCost file is a list of materials and the price per unit of the material.   I need to get the price of each of 10 different elements in my source file from the MatCost file.  Right now, I'm using 10 individual @XLookup commands.

Any suggestions?

NHUser
  
Back to top
 
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: Gathering Data from Other Files
Reply #5 - Jun 29th, 2011 at 1:51pm
Print Post Print Post  
When we have worked with Q&A for a long time, mind refuses to go beyond xlookups.  I am not sure if I understood your question right, but it seems you can accomplish that using XResultSet commands. It seems a little daunting at first but once you start working with it, you realize you can do a lot more and a lot more efficiently.
  
Back to top
 
IP Logged
 
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Re: Gathering Data from Other Files
Reply #6 - Jun 29th, 2011 at 7:49pm
Print Post Print Post  
I've been looking at the XResultsSet() commands and I think they have the same problem.  I'll try to better explain what I am trying to do:

I have a file, Material Cost, that stores two bits of data, a material number and the cost per unit.  So if i downloaded the file to a spreadsheet, I would have two columns of information with the headings "Material Name" and "Price".  For example:

Material Name     Price
Popcorn              2.75
Cracker Jacks       0.82
Peanuts              1.72
Potato Chips        0.98

I want to be able to select two items from this file using mass updating programming from another file, say, Inventory Value.  The info in different elements in Inventory Value will need one of the prices from the the Material Cost list.

My dilema is that I don't know how to call for data from the Material Cost file without putting a link between the two files.  It seems that both @XLookup and XResults() require me to tell them the Material Name (as defined by an element in my Inventory Value file) in order to get the price.  If I have to tell them the Material Name, then I have to write an new command statement when I try to get the data based on a second element.

Any advise?

NHUser
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Gathering Data from Other Files
Reply #7 - Jun 29th, 2011 at 7:50pm
Print Post Print Post  
NHUser wrote on Jun 29th, 2011 at 1:33pm:
Is there an easy way to gather a series of data from a file (called MatCost) that are based on different elements in my source file?

For example, the MatCost file is a list of materials and the price per unit of the material.   I need to get the price of each of 10 different elements in my source file from the MatCost file.  Right now, I'm using 10 individual @XLookup commands.

Any suggestions?


Keep in mind that the X commands can accept a retrieve spec as the key value. You're not limited to a single value. For example, the following code displays the zip code from five different records in the sample Customers database.

Code
Select All
var vList as String

	vList = @XLookupAll(@FN, "937;938;939;940;941", "KEY", "Zip")
	WriteLn(vList)


Displays:
77444;05040;15166;15124;84488
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Re: Gathering Data from Other Files
Reply #8 - Jun 29th, 2011 at 8:56pm
Print Post Print Post  
Thanks for the hint!  But, I'm still having trouble.  I put the following in my programming:

Var ReturnVal5 as string

ReturnVal5=@XLookUpAll(@FN,"elastomer;fabric;insert1;insert2;insert3","MATERIAL COST LIST!MATERIAL NUMBER","Material cost")

writeLN(elastomer,fabric,insert1;insert2;insert3;ReturnVal5)

It doesn't cause any problems, and my writeLN statement lists the correct material designations, but the ReturnVal5 string is empty.

Is there something wrong with my statement?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Gathering Data from Other Files
Reply #9 - Jun 29th, 2011 at 9:18pm
Print Post Print Post  
NHUser wrote on Jun 29th, 2011 at 8:56pm:
ReturnVal5=@XLookUpAll(@FN,"elastomer;fabric;insert1;insert2;ins
ert3","MATERIAL COST LIST!MATERIAL NUMBER","Material cost")


The way you have it now, it is looking for a record in MATERIAL COST LIST with a MATERIAL NUMBER literally of "elastomer" instead of the value in the elastomer element on your form. I'm betting that what you want is something like this:

var vMyKey as String

       vMyKey = elastomer + ";" + fabric + ";" + insert1 + ";" + insert2 + ";" + insert3
       ReturnVal5=@XLookUpAll(@FN, vMyKey, "MATERIAL COST LIST!MATERIAL NUMBER", "Material cost")
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Re: Gathering Data from Other Files
Reply #10 - Jun 29th, 2011 at 9:27pm
Print Post Print Post  
Your right.  I'm looking for the price of the value in the elements called elastomer, fabric, insert1, etc.

I don't understand the "+" sign your adding.  You changed "elastomer" to "elastomer +" but you changed "fabric" to "+ fabric +".  What does the "+" do?

NHUser
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Gathering Data from Other Files
Reply #11 - Jun 30th, 2011 at 1:07pm
Print Post Print Post  
NHUser wrote on Jun 29th, 2011 at 9:27pm:
Your right.  I'm looking for the price of the value in the elements called elastomer, fabric, insert1, etc.

I don't understand the "+" sign your adding.  You changed "elastomer" to "elastomer +" but you changed "fabric" to "+ fabric +".  What does the "+" do?


Let's say you have the following values in your five elements:
elastomer - EL1
fabric - FB12
insert1 - IN302
insert2 - IN224
insert3 - IN006

You would need the key value for your X command to end up as this:
"EL1;FB12;IN302;IN224;IN006"

So, you need to assemble that value by concatenating (adding together) the values in each of the five elements together with semicolons between them. The value in the elastomer element, plus a semicolon, plus the value in the fabric element, plus a semicolon, etc...

vMyKey = elastomer + ";" + fabric + ";" + insert1 + ";" + insert2 + ";" + insert3
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
NHUser
Full Member
***
Offline



Posts: 320
Location: Amherst, NH
Joined: Aug 2nd, 2010
Re: Gathering Data from Other Files
Reply #12 - Jun 30th, 2011 at 1:57pm
Print Post Print Post  
Understood.

I've updated my programming and it's working great!

Thanks for the help!!

NHUser
  
Back to top
 
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