table relationships

[StanKorn]

Is there a way to get a map or listing of tables and how those tables are related? I am working with a trucking app and need to at least output table structure out so I can try to build the map myself. The DBF are not normalized and ideally I would like to try and accomplish this as an intermediate term project. The app is VFP 7 I have VFP 6 but my knowledge of it is at the very steep end of the learning curve. Can I do this with my current version of VFP? Any help would be greatly appreciated


Remember - only dead fish go with the flow.

 

[mkrausnick]

It depends on whether your trucking app is using VFP tables or is a front-end for a client-server database like SQL Server or Oracle. If the data is stored in Foxpro tables, VFP comes with an application called ANALYZER that will document your application, sort of.

If the writer of the application built a DBC database container, some relationship information might be stored there. Most programmers don't put relationship info there because it doesn't do any good other than for documentation.

You can also write a simple program to do a LIST STRU on each table to get the columns, and here's a simple program you can use to list each tables indexes:

*
*  TAGS.PRG - List a table's tags
*

local nTags, i, lUsed, cAlias, cFile
SET MESSAGE TO 'Select table'
cFile = GETFILE("DBF", "Select table", "OK", 0)
SET MESSAGE TO
if !empty(cFile)
  cAlias = juststem(cFile) 
  lUsed = used(cAlias)
  if lUsed
    select (cAlias)
  else
    use (cFile) alias (cAlias)
  endif
  select (cAlias)
  nTags = TagCount()
    set safety off
  set alternate to tags.txt
  set alternate on
  set safety on
  for i = 1 to nTags
    ? str(i)+".  "+left(tag(i)+"              ",14)+sys(14,i)
  next i
  if !lUsed
    use
  endif
endif
set alternate to
set alternate off
? "Tag list in TAGS.TXT"

This VFP7 program asks you to pick the file and then puts the tag list into a text file in addition to displaying it. I then paste the text file into my program documentation.

You can replace the GETFILE() in this code with, say, an array containing the names of the tables, or an array containing the results of an ADIR() to get the list.

Good luck and post back with more questions.



Mike Krausnick
Dublin, California

 

[StanKorn]

Hi Mike,
Thanks for the info. THe app is VFP 7 but not built utilizing DBC. THe tables are not normalized and there are 4 distinct apps within the program - Dispatch which feeds Trip Interface which feeds Trip Settlement which feeds Billing. To further muddy the waters Trip Interface and Trip Settlement also feed Accpac Pro AP and PR modules but the developer won't share the how with me. Billing interfaces directly to GL in Accpac and again the developer won't share the info re how the interface is accomplished. By feeding GL direct, they bypass the AR module and basically invalidate any benefits that are driven by that app. Their view is that the Billing app does everything we need (no it doesn't but that is another chapter). I am trying to build the maps that I need so I can perform the tasks that the company requires.
I come out of an Oracle environment for the past 8 years and am deserately trying to overcome the learning curve on VFP. I appreciate your assistance but a rather dumb question for yopu - where would I execute the prg you sent in your last post. I feel very foolish asking such basic questions but the VFP world does not look anything like the Oracle world I come from <g>.
thanx much,
stan


Remember - only dead fish go with the flow.

 

[mkrausnick]

I know what you mean - VFP didn't look anything like what I came from either when I first started. But it's actually very friendly to work with.
You indicated that you have VFP6. That would be the place to start. When you run VFP6, you get a blank white screen with a command window in it. The command window is where you enter all your commands. Results of commands are displayed in the screen area unless you specify otherwise. In this mode, VFP is an interpreter - your procedural commands are compiled and executed immediately.

VFP has a robust procedural language to go along with the database handling. You can create program files and execute them realtime without having to compile them - VFP compiles them automatically.

So, to get you started, copy the code in my prior post into the clipboard, then type:

MODI COMM TAGS

in the command window. This is short for

MODIFY COMMAND TAGS.PRG

which is VFP-ese for

"EDIT TAGS.PRG".

Then paste the clipboard into the edit window that popped up when you entered the MODI COMM command.

Click the Save icon and then the RUN icon. YOu might have to move the edit window to see the results.

One trick that will save you much confusion about the VFP development environment is that the commands in the command window can be re-executed simply by placing the cursor on the line of the command you want to re-execute and pressing enter. Only the command on that line will re-execute. Commands below or above will not. So if you execute TAGS.PRG and want to run it again, just click on the line that says DO TAGX.PRG and press enter.

If you don't have a VFP reference book and you're going to be using VFP for awhile, you should invest in one. I like the Microsoft Press books best myself, but there are lots of them. You can't go wrong with any book by Tamar Granor.

Lastly, I would caution about using VFP6 to mess with a VFP7 application. I would recommend getting VFP7, especially if you will be modifying the application. Later on you're going to want to upgrade to VFP8.

VFP6 should be OK to look at the data though.

Oh, one last thought: If your a DBA-type, you need to know that pre-VFP8, SQL is not as robust as you are used to. So if you execute a SELECT statement in the command window and it gets a syntax error, you are probably doing something VFP's flavor of SQL doesn't support.

Hope this helps!

Mike Krausnick
Dublin, California

 

[StanKorn]

Thanks Mike.
Life is a lot easier knowing you are out there.
I'll keep you posted on my progress.

regards,
stan


Remember - only dead fish go with the flow.

 

[StanKorn]

Mike, need help.
I just updated the MTI software and on three of the 6 machines in the office I get the following error:

A required .DLL file, MSVCR&).DLL was not found

I am in deep doodoo bcoz the developers have quit for the day.
can you offer any advice?
thanx,
stan


Remember - only dead fish go with the flow.

 

[mkrausnick]

No, I've never heard of that. It sort of looks like a garbled message. I'd start by comparing list list of DLLs on a machine where the upgraded app works to the list of DLLs on one of the machines where it doesn't work. If you're lucky (I seem to be using that phrase a lot lately) the DLLs are kept in a sub-directory of the app's directory rather than in SYSTEM32.

Also, check the project file on the development machine (that's the file with the PJX extension - the DLL might be listed there under API LIBRARIES in the CODE tab or OTHER FILES in the OTHER tab, although it's not required. The command is:
MODI PROJ <projectfile>. Highlight the library if you find it and the physical location is shown under the list window in the &quot;Description&quot; box.

All this assumes you have access to the source code!

Good Luck,



Mike Krausnick
Dublin, California

 

[StanKorn]

Hi Mike,
I traced thru the DLL's on each macine in the office and 3 of the 6 had the DLL and three did not. I took a shot that it may have been the VFP runtime that somehow got mangled so I loaded run for VFP 7 and that solved the problem.
But it raised another issue - the developer has disabled all licences for the app when the current maintenance & support contract expires so my guess is that we will need to pay the $10K to renew the contract as of 8-1-2003. This software only cost the company abput $50K when they bought it in 1999 and I can't reconcile a $10K support cost for this app especially since they don't want to help me with issues. The company was still on the original versions of the app that were installed 4 yrs ago. To me this takes tremendous gall to charge a client so much for something that is adequate at best. Is this normal for developers to do such licensing schemes that force a client to pay exhorbitant support fees in order to maintain a licence on software. I have never seen such a scheme in 20 yrs of dealing in this business.
thanks,
stan


Remember - only dead fish go with the flow.

 

[Mike Gagnon]

StanKorn

A required .DLL file, MSVCR&).DLL was not found

I traced thru the DLL's on each macine in the office and 3 of the 6 had the DLL and three did not. I took a shot that it may have been the VFP runtime that somehow got mangled so I loaded run for VFP 7 and that solved the problem.

The two above statements sounds like, someone just copied the application over to another machine thinking it would work.

I can't reconcile a $10K support cost for this app especially since they don't want to help me with issues.

If they understand that the application was just copied over, I can see why they don't want to help.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.