search a common field value from different tables from an specific directory

[titoneon]

Hi Experts,
I would like to see if anyone can help me with some ideas

Here is the scenery, i have a mapped drive X:\ in my computer that point to a server directory, in that directory i have a bunchs of tables, example 92110.dbf, 11015.dbf, 130150.dbf and so on.

all this tables has the same fields in common example job_no, draw_no, part_no etc.

what i need to accomplish, is to be able to type a particular "part_no" value in a textbox and find all the job_no in which this part_no is contained, of course this will involve to go throughout all the tables in the directory that is in X:any help is very appreciated
Thanks a lot

 

[Olaf Doschke]

That has been answered partly in thread184-1733961

eg you can use ADIR to get an array of all DBFs;

lcPath = "X:\"
lnCount = ADIR(laFiles, ADDBS(lcPath) + "*.dbf")
FOR lnI = 1 TO lnCount
  lcFile = FORCEPATH(laFiles(lnI, 1), lcPath)
	? "Now searching " + lcFile
  USE (lcFile) Alias Jobs In Select("Jobs")
  * Process Jobs
ENDFOR

Part 2, searching the table alias Jobs is as easy as if you had one DBF: Select * From Jobs where part_no = yoursearchvaluehere, eg ... where part_no = "ABC123"

Part 3 is adding all the single results into one. Do that with APPEND.

Still questions? Have a try, extend the code and come back with your version to complete this.

Bye, Olaf.

 

[Mike Lewis]

Here's a variation of the above code. I haven't tried to test it, but I think it will do the whole job for you.

SET PATH TO = "X:\"
lcPath = "X:\"
lcSearch = "123-456"   && this is the part no. we are searching for
lnCount = ADIR(laFiles, ADDBS(lcPath) + "*.dbf")
lcCmd = ""

* Build a SELECT ... UNION ... INTO command
FOR lnI = 1 TO lnCount
  IF lnI > 1
    lcCmd = lcCmd + " UNION "
  ENDIF
  lcCmd = lcCmd + " SELECT Job_No FROM " + laFiles(lnI, 1) + ;
    " WHERE Part_No = " + lcSearch
ENDFOR 

IF EMPTY(lcCmd) 
  * No files found; do nothing
ELSE 
  lcCmd = lcCmd + " INTO CURSOR csrResults"
  
  * Execute the command
  &lcCmd
ENDIF

Once you've run the code, all your results should be in the cursor csrResults.

One point about this approach is that it will automatically remove any duplicates (which I assume is what you want).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Hi Mike,
well i just need to find for example in what job_no it is included "123-456" and each table i have in that directory is named as a job_no.
let me repeat this better for example this part_no="123-456" could be in table 11315.dbf as well could be in 92100.dbf and so on of course each of these table has a field job_no and part_no, i just need to display all the job_nos that include such a part_no

I ran your code and i am getting when trying to display the &lcCmd this message line to long, i did browse the cursor and it only display the content of the first table where that part_no exist, since the error appears, i have canceled the execution of the program, that is why only one table job no is shown
Thanks

 

[titoneon]

Hi Olaf,
This is what i did but i am getting this message "VARIABLE PART_NO NO FOUND " this happen when the line below is executed

"Select * From Jobs where part_no = LCPART_NO INTO CURSOR junkjobs readwrite"

then i press ignore until i go throughout all tables then i am able to browse the table "MYJOBS" and i got there all the job_nos where part_no="92110-311" exist
Please let me know what modifications i need to get it right if you can
THANKS

lcPath = "X:\"
SET CPDIALOG OFF
LCPART_NO="92110-311"
CREATE TABLE MyJOBS (JOB_NO C(6), PART_NO C(10)) 
lnCount = ADIR(laFiles, ADDBS(lcPath) + "*.dbf")
FOR lnI = 1 TO lnCount
  lcFile = FORCEPATH(laFiles(lnI, 1), lcPath)
	? "Now searching " + lcFile
  ? lcfile
  
  USE (lcFile) Alias Jobs In Select("Jobs")
    * Process Jobs
  Select * From Jobs where part_no = LCPART_NO INTO CURSOR junkjobs readwrite
  IF _TALLY<=0
    ? " no data here "
  ELSE
   STORE JOB_NO TO LCJOBNO   
   INSERT INTO MYJOBS(JOB_NO, PART_NO) VALUES (LCJOBNO, lcPART_NO )
  ENDIF   
ENDFOR 

SELECT MYJOBS
BROWSE

 

[Mike Lewis]

well i just need to find for example in what job_no it is included "123-456" and each table i have in that directory is named as a job_no.
let me repeat this better for example this part_no="123-456" could be in table 11315.dbf as well could be in 92100.dbf and so on of course each of these table has a field job_no and part_no, i just need to display all the job_nos that include such a part_no

Yes, that's pretty well what I understood. And it's what my code is intended to achieve.

I ran your code and i am getting when trying to display the &lcCmd this message line to long

At the point at which you are trying to run the command, can you tell me how long the command is? If you suspend execution at the [tt]&lcCmd[/tt] line, and then check [tt]LEN(lcCmd)[/tt], that would give the required length.

The maximum command length in VFP is 8,192. If lcCmd is more than that, how many tables have you got in the directory? If it is a high number, can you try running the code with just three or four tables, just for testing purposes. Once you've got that working, we can look for ways of reducing the command length.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Mike,
I will do that and get back to you, but the lnCount in Olaf code, display the value of 2,123

 

[titoneon]

Mike,
If i use the code Olaf posted plus the lines i added it should work, do not misunderstand me, your code will work of course with some little changes, the point is, that reason i am getting "variable part_no no found" is cause the code is searching in each table that is in that directory, there is one thing i forgot to tell you, each table that has a number for example 11235.dbf also there is another table with the same number but ended with "A", I just need to find the part_no value on those dbf file that are not ending with letter "A", Can you tell me how can exclude from the search tables ending just with the letter "A" PLEASE ?
Thanks

 

[Mike Lewis]

Can you tell me how can exclude from the search tables ending just with the letter "A" PLEASE ?

That should be easy enough.

After this line:

[tt]lcFile = FORCEPATH(laFiles(lnI, 1), lcPath)[/tt]

you should add a test, like this:

[tt]
IF UPPER(RIGHT(JUSTSTEM(lcFile), 1)) = "A"
LOOP
ENDIF[/tt]

I think that will do it.

the lnCount in Olaf code, display the value of 2,123

Yes, that is quite a large number. You could try putting the same test (for filename ending in "A") in the equivalent place in my code, but it would probably still be too many files for one UNION SELECT command.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Okay, the problem is two DBF types.

In principle we have three solutions:
1. Nevertheless open all tables and check, whther a field Part_no exists via TYPE() or VARTYPE().
2. Use the file skeleton of the ADIR command more specific than *.dbf (all dbfs). Unfortunately you could only specialise in finding *a.dbf, all DBFs you don't want and then won't have the DBFs you want to search in.
3. Check the file name in lcFile by checking if UPPER(RIGHT(JUSTSTEM(lcFile),1))="A"

Using solution 3:

lcPath = "X:\"
SET CPDIALOG OFF
LCPART_NO="92110-311"
CREATE TABLE MyJOBS (JOB_NO C(6), PART_NO C(10)) 
lnCount = ADIR(laFiles, ADDBS(lcPath) + "*.dbf")
FOR lnI = 1 TO lnCount
  lcFile = FORCEPATH(laFiles(lnI, 1), lcPath)
  If UPPER(RIGHT(JUSTSTEM(lcFile),1))="A"
     Loop && Don't process this file
  Endif
  ? "Now searching " + lcFile
  USE (lcFile) Alias Jobs In Select("Jobs")
  * Process Jobs
  && As you created a resulttable, you can insert into that rightaway, so No more: Select * From Jobs where part_no = LCPART_NO INTO CURSOR junkjobs readwrite
  Insert Into MyJobs Select Job_No, Part_No From Jobs where part_no = LCPART_NO
  IF _TALLY<=0
    ? " no data here "
    * That won't matter much
  ELSE
   * The Cursor "junkjobs can have multiple records, you just fetch the first with this code
   && No: STORE JOB_NO TO LCJOBNO   
   && No: INSERT INTO MYJOBS(JOB_NO, PART_NO) VALUES (LCJOBNO, lcPART_NO )
  ENDIF   
  * The whole IF _TALLY part could be removed.
ENDFOR 

SELECT MYJOBS
BROWSE

As you filter for a specific part_no, all part_nos would be the same in the result table, unless the part_no field is longer than "92110-311". If that is the whole part_no the only other reason you put it into the result is, you want to add more results with other part_nos into the same table.

Bye, Olaf.

 

[titoneon]

Olaf
Thanks a lot, will do that, thanks also for the corections and the added code

One more thing, i just forgot since i have other dbf files in there that start with letter and some of them are letter combined with number, i need to avoid the code to look into it so otherwise it will stop with error in the line " Insert Into MyJobs Select Job_No, Part_No From Jobs where part_no = LCPART_NO", i just need to look in dbf files that are absolutely numbers (example 114526.dbf) no letter at the begining or in the middle either


Mike,
Yes definitive they are a lot of tables even if we don't include the one ending with "A"
Thanks a lot

 

[Olaf Doschke]

In short: Same data belongs in one DBF. Well, if you only need <<digits>>.dbf files, then filter that in the line If UPPER(RIGHT(JUSTSTEM(lcFile),1))="A". You need some IF condition testing, whether the name contains something besides digits.

Look at CHRTRAN(JUSTSTEM(lcFile),"0123456789","").

Bye, Olaf.