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

[A_Radiolog_inFoxPro]

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.

 

[Chris Miller]

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:

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

 

[A_Radiolog_inFoxPro]

Dear Sir ,

I am an idiot with a task to deal with FOX pro (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

 

[Chris Miller]

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:

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:

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

 

[Mike Lewis]

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

 

[Tore Bleken]

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.

 

[Mike Lewis]

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

 

[A_Radiolog_inFoxPro]

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

 

[Chris Miller]

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:

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

 

[A_Radiolog_inFoxPro]

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.

 

[Chris Miller]

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
or literally
[ignore]

https://www.tek-tips.com/viewthread.cfm?qid=1828035

[/ignore]

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

 

[A_Radiolog_inFoxPro]

Dear All ,
Thank you for all the help , I managed to get out of the mess I got my self into.

Now I have an other question.

If I want to delete the table and re-create it every time the routine runs to be sure all data are removed how can I do it ?

for the time being I am using ZAP a quite dangerous command. and I am a bit worried that at some point something will happen and the software will be suck somewhere that it shouldn't be and zap some other selected database instead the temp table.

Safety is off so all bets are off. (both of my data and life ) (although, I haven't pushed it into prod yet.)

Let me know how I can delete the table files after using them. (without getting the files is in use after the first run)

Thank you in advance
The Doc.

 

[Chris Miller]

Please, don't add a new question, even though I see some resemblence, to such an old thread.

If you want to pick up something you started a month ago, there is a thread identifier at the start of every thread, you can simpy write into a new threads initial post. For this thread the identifier is

thread184[ignore]-[/ignore]1828035, so you could start a new thread with a new subject line and start it by saying something like

in thread184[ignore]-[/ignore]1828035 I asked how to open two tables, now I have a related question...

If you post or preview it, you'll see that thread number will automatically expand to a link to this old thread, so anyone interested to see the earlier discussion can do so.

Chriss

 

[A_Radiolog_inFoxPro]

Thank you already done.
Merry Xmas and happy holidays <3
Thanks
The Doc