×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.
9

thread184-1828035

How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

(OP)
Dear All ,

I am in need of your assistance , experience , and expertise in this small trouble I have.

I am gathering some data from a line by line formatted file , and I insert the data into a table I made. ( 1 record several columns , don't ask me why I didn't use an array in the first place , beats me ...anyway )

Now I have the following issue.
I need to get the index of the gathered data table ( for example some id code of said record ) and check if this ID exists in the other table.

After that is done I need to decide if there are data already in the other table , do they differ , should I insert the captured data and so on and so forth.

But as far as I remember I can USE only 1 table at a time.

is there a way around this ? ( without having to have a LOT of variables to copy data from 1 table hold them in memory and replace them on the other table ? ) to avoid mistakes and messy for loops.

Thank you in advance

The Idiot.

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

One table at a time? Really?
Why are there multiple lines in the data session window, then?

Are you sure you are talking about Foxpro?

Well, in short:

CODE --> 

USE table1 in 1
USE table2 in 2

Now you already have 2 tables open.

I don't know which version of FoxPro you run, I know early versions only had 15 workareas and maybe FoxPro 1.0 had even fewer.

Anyway, Select(1) - which by documentation returns the highest unused workarea number - is 32767 for VFP9.

So, how do we say? Plenty. I think your actual problem will be something else than opening more than 1 table. Feel free to ask further questions.

Chriss

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

(OP)
Dear Sir ,

I am an idiot with a task to deal with FOX pro :D (foxpro 6.0)

I am trying to do my best ( I used to work with VB and java ... ) ancient times now I became rusty that's why I am kindly asking for your enlightenment as it shows, I still have much to learn.

Regarding USE 1 table at time this was my assumption at the time ( not anymore )

But an other question how do I move to the next record knowing in what table I am moving to the next record ( how do I make sure I am moving the proper pointer ? )

Thank you so very much sir ,

The idiot :D

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

Now you're really asking for atoms. You don't need to do things like that yourself.

Select a workarea, then you can SCAN..ENDSCAN, so for example:

CODE --> 

USE table1 in 1
USE table2 in 2
Select table1
Scan
    If Seek(table1.id,"table2","id") && provided table 2 has an index on id
       * Found record of table1 in table2, now decide to keep the table1 record
    Endif
Endscan

That already makes stepping through table1 automatic without even knowing a command that moves a record pointer. It's SKIP, if you really want to know, still.

And you can take it even on a higher level where you don't even need to know a command to open a table, you can use SQL in VFP, also in VFP6:

CODE

Select * from table1 left join table2 on table1.id = table2.id
This a) opens table1 and table2, b) moves through records, 3) joins them when possible and does so much more in one command where you want to write a novel of code, instead.

In that result every record in table1 that is new has NULLs in the columns that come from table2, if a record with same id exists. That means, every record that already exists in table2 with the same id will be put side by side, which you therefore can compare. And that can be further porocessed by whatever rules you have to keep or update or scrap a record from table1. And there surely are gazillion of other ways to do this, instead of just joining records, you can also use update with conditions, etc, so you can put finding the similar record and updating in one update statement. And you also can write an insert query that concentrates on the missing records and inserts them.

Chriss

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

Quote:

I can USE only 1 table at a time

Even in the days of dBASE II - back in pre-history - we could open two tables at the same time.

Ah, what fun we used to have, updating one table with data from a second that was validated against a third.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

5
With all respect, stop calling anyone an idiot, not even yourself. If you want to get help, don't hide behind a stupid mask. If you absolutely feel that you must use an alias, at least use something useful or meaningful.

Show respect, and you will receive respect. Hiding behind a mask doesn't give you much credit.

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

2
What Tore is trying to say is that it is usually considered polite for forum members to use their own names, rather than a pseudonym. If you look at the other posts in this thread, you will see that we all do that. Of course, you might have a good reason for not wanting to publicise your own name, which is OK. In that case, by all means use an alias, but it should at least be one that we address you with.

You won't have to create a new account for this. Just click on My Stuff (at the top right of the screen) and choose My Profile. You will see a space where you can enter a "display name".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

(OP)
Dear All ,

Yes I understand that my screen name was a bit ... unorthodox. But I like to be up-front and literal about my situation.

I am a resident radiologist (medical doctor) with a passion for computing and I am maintaining an old medical software.

Most of the times I forget what I learned last time because I don't keep up with my programming. ( if you don't use it you lose it )

Never the less, I changed it as I feel / with your input and out of respect for your help through out the years.

Thank you in advance for all your help.

A_Radiolog_in_FP

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

That's okay, this forum isn't only for experts.

I wonder how much you could have extracted from the code you already have. I mean remembering something wrong has happened to anyone of us, surely, the brain isn't perfect in memorizing.
Having at hand code of a project of a medicinical software makes me wonder a bit, though. Seems even looking at that code is just like looking at cryptic messgages or a foreign language to you.
I can therefore only recommend you put somthing in the code you can understand even after 10 years: English.

You can write comments into your code, if you start a line with * or at the end of a line of code after &&, like this:

CODE

Open Database Home()+"Samples\Tastrade\Data\Tastrade.dbc"
Use customer
* Close Databases all && uncomment this to demonstrate the error case
* usage example of function Search()
Search('London')

Function Search(tcSearchterm as String)
    * Searches a passed in string (tcSearchterm ) in the current workarea
    * This only works in the IDE, not in an EXE, as the search form 
    * is only present in the IDE, not in the runtime DLLs
    If Alias()=='' && There's nothing open in the current workarea
       Error "You can't find anything in a void. Open a table or view in the current workarea before using the Search() function"
    Endif
    
    Browse Nowait && browse the current workarea and continue with executing code (therefore nowait)
    Keyboard '{CTRL+F}'+tcSearchterm+"{ENTER}" PLAIN CLEAR && CTRL+F starts the "Find" dialog
    * tcSearchterm is entered into "Look for" and ENTER initiates the first search.
    * clicking "Find next" finds further instances of tcSearchterm

This is extremely commented code, I would write less than that for myself, but you know, you have means to make your code understandable to your future self.

I'm not sure if you have the testrade database in VFP6, but I think this sample database is available for longer than Northwind sample data, maybe it's not in that directory.
You can use the search function sample with any other table, though.

And please, don't argue with that taking too much time. You waste time by having uncomented code you then can't easily extend yourself, as it becomes a riddle to yourself, no matter if it's code you got from a forum or your own.

This is one of the ingredients we also use ourself to stay sane.

Chriss

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

(OP)
Dear Mr.Chriss ....

I have several thousands lines of code ( with several programs and subroutines) without a single comment in it.

It's all guess work how I managed to understand what everything does at the end of the day.

Thanks

The Radiologist.

RE: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.

Well, that's bad

But great, you can start with that. The least thing you could put into a comment is a link to this thread:
thread184-1828035: How to use 2 tables at the same time and use one filed from 1 to check its existence in the other.
or literally
https://www.tek-tips.com/viewthread.cfm?qid=1828035

Besides you can archive a thread, if it is really important for you. It's not helpful to do this with all threads, because they are listed in "My Stuff" "My Threads" anyway.
And please, don't just refuse that idea because you say you know yourself and it won't help you. When you come up with a question along the lines "I have this code... I want to add in the feature of ..., then it will help us, the experts to heve even the slightest comments available, if it only is telling in natural language what the whole code does, not line by line, that's also already helpful, and in turn, that helps you. If you don't keep the least reference and comment and even remove them from code you got from forums, that's actually also a form of self harm, not at all healthy.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close