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.
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.
Why are there multiple lines in the data session window, then?
Are you sure you are talking about Foxpro?
Well, in short:
CODE -->
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.
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.
Select a workarea, then you can SCAN..ENDSCAN, so for example:
CODE -->
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
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.
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.
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.
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.
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.
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
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.
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.
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