English

How To: Find joined or linked tables and fields in a project.

Summary

This article provides a script to determine which tables in a project are joined or linked.

Procedure

The script will choose a project file from disk and return the names of joined or linked tables. It will also return the fields used to create the join or link. Results are returned in a message box. This script should be run in a new project.

  1. Open a new script window.

    A. Activate the Project window.
    B. Click the Scripts icon.
    C. Click New.

  2. Copy the code into the new script window.

    Code:
    '---------------Begin Script------------------
    'Will return a message box for each instance of a Join or Link
    'The following information will be returned:From Table, To Table, From Field, To Field.

    afn = filedialog.show("*.apr","AV Projects","")
    lf = linefile.make(afn, #FILE_PERM_READ )

    joinDict = dictionary.make(10)
    linkDict = dictionary.make(10)

    while(lf.isatend.not)
    buf = lf.readelt
    if((buf.contains("(Join.")))then
    joinobj = buf.astokens(".").get(1)
    fromt = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    fromf = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    tot = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    tof = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    joinlist = {fromt,fromf,tot,tof}
    joinDict.add(joinobj,joinlist)
    elseif(buf.contains("(Link.")) then
    linkobj = buf.astokens(".").get(1)
    fromt = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    fromf = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    tot = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    tof = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    linklist = {fromt,fromf,tot,tof}
    linkDict.add(linkobj,linklist)
    end

    end
    masterJoinList = {}
    masterLinkList = {}

    for each jlist in joinDict
    newlist = {}
    for each elem in jlist
    lf.gotobeg
    while(lf.isatend.not)
    buf = lf.readelt
    if((buf.left(1) = "(") and (buf.astokens(".").get(1) = elem)) then
    'msgbox.info(buf,"")
    newobj = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    newlist.add(newobj)
    end
    end
    end
    masterJoinList.add(newlist)
    end

    masterLinkList = {}
    for each llist in linkDict
    newlist = {}
    for each elem in llist
    lf.gotobeg
    while(lf.isatend.not)
    buf = lf.readelt
    if((buf.left(1) = "(") and (buf.astokens(".").get(1) = elem)) then
    'msgbox.info(buf,"")
    newobj = lf.readelt.astokens(":").get(1).basictrim(TAB,"")
    newlist.add(newobj)
    end
    end
    end
    masterLinkList.add(newlist)
    end
    MsgBox.Info("This project contains"++masterJoinList.count.asstring++"joined table(s).", "Joined Tables")
    Count = 1
    for each l in masterJoinList
    msgbox.listasstring({"TO TABLE:"++l.get(0).unquote, "TO FIELD:"++l.get(1).unquote, "FROM TABLE:"++l.get(2).unquote, "FROM FIELD:"++l.get(3).unquote},"Join Table"++count.asstring,"Joined Tables and their Fields")
    count = count + 1
    end
    if (masterJoinList.isempty) then
    msgbox.info("This project contains no Joins","")
    end
    MsgBox.Info("This project contains"++masterLinkList.count.asstring++"linked table(s).", "Linked Tables")

    Count = 1
    for each l in masterLinkList
    msgbox.listasstring({"TO TABLE:"++l.get(0).unquote, "TO FIELD:"++l.get(1).unquote, "FROM TABLE:"++l.get(2).unquote, "FROM FIELD:"++l.get(3).unquote},"Link Table"++count.asstring,"Linked Tables and their Fields")
    count = count + 1
    end
    if (masterLinkList.isempty) then
    msgbox.info("This project contains no Links","")
    end
    '-----------------End Script--------------

  3. Compile the script by clicking the compile button.
    [O-Image] Script compile button
  4. Run the script by clicking the run button.
    [O-Image] Run compiled script button