relationship between 2 tables

[sprezzaturon]

I may be making this more difficult than necessary but I'm trying Strata on a Parts Inventory Database. I've picked two tables out of the database: one has parent part numbers with their associated child part numbers (for example, part A is made up of part B, C and D; part E is made of part C, F and G), the other contains just part numbers and their descriptions. I was trying to have the descriptions show up along side both the parent and child part numbers columns but I can only have descriptions showing for one column or the other. I tried created two copies of the part description table and then created the relationship with the parent number to its counterpart in one table and the child number to the other table. But the link for the child to description would disappear after I closed the relationship window and reopened it.

Is what I'm trying to accomplish feasible with Strata? If so, what am I doing wrong?

Thanks for your help,
Ron

[Aaron]

Hi Ron,

What you want to do is definitely possible.

If I understand correctly, you have two tables. The first table is an item number cross-reference table with several columns of item numbers, where one of the columns is the parent item number and the other columns contain child part numbers that correspond to the parent. The second table is an item number description table that has at least two columns, where one column contains both parent and child part numbers and the other column contains corresponding descriptions for those parts. Using these tables, you want to bring the item description from the item description table into the item cross-reference table for each item in each of the columns of the item cross-reference table.

Your intuition is right: you need to create a copy of the item description table for each item column so that you can set a relationship between the item cross-reference table and the item description table on each of the columns in the item cross-reference table. The following screenshots show this:

After copying each of the item description tables, we have:

screenshot1.png (39.52 KiB) Viewed 95309 times

Then, setting up the relationships, we have:

screenshot2.png (29.38 KiB) Viewed 95309 times

Finally, after inserting the item description field from each of the item description table copies, we have:

screenshot3.png (41.58 KiB) Viewed 95308 times

As you expected, this works, and it should work in your case as well. If this isn't working, here's several things to check:

1. Make sure the tables are internal to Strata's project and not externally connected; Strata does not support setting up relationships to externally connected tables when the externally connected tables are child tables in the relationship.

2. Make sure the table names don't have any spaces in them.

3. Make sure the relationship diagram is using each of the item description table copies only once instead of using one of the item description table copies multiple times.

See if this works. If not, please let me know more so I can help you further!

Best,
Aaron

[sprezzaturon]

Thanks, Aaron. Once I renamed my copied table differently from its original (external connected) name, the spreadsheet worked as expected.

Since I'm working with copied data (internal connected), do these tables update themselves from the external tables each time I run Kirix?

Thanks,
Ron

[Aaron]

No, if the tables are copied, the data in the copied tables doesn't update when the data in the original tables change.

[sprezzaturon]

So to keep my data current, is my only option to delete the copied tables, then recopy and rename them and then recreate the relationship on a daily basis?

[Aaron]

Yes, in a relationship, the child table can't be an externally linked table, so you have to copy it into Strata each time you want to perform this analysis. If you are running this process every day, and don't want to keep copying tables and setting up relationships on them, I can show you how to create a quick script that will create a similar output table to the one you are creating with the relationships.

[sprezzaturon]

Yes please, Aaron, I would greatly appreciate learning about that script.

[Aaron]

Ok, here is a sample script that merges two sample tables, as described previously in this thread. To try out the script:

1. Download script_example.zip and unzip the contents
2. In Strata, open the part_table_merge.js script from the unzipped contents with File->Open
3. Similarly, import the sample tables to your Strata project from the part_tables.kpg package file with File->Import
4. Run the script with Tools->Run Script/Query
5. If everything works properly, you should see three tables in your project: part_descriptions, part_numbers, and part_merged_output

To see how the script works, open the script and read through the comments/code in the script. To adapt it to your specific application, you'll have to change the database connection info to appropriate values for the database where your item data is stored, as well as change the field names to correspond to the fields that contain the item number and description data.

Much more is possible with scripting, and hopefully this will get you started. See http://www.kirix.com/extensions/developer-resources.html for more information.

Let me know how this works!

Best,
Aaron

[sprezzaturon]

Wow, thanks Aaron! Looks like I've got my weekend work cut out for me now.

By the way, how do you make a connection using ODBC? I'm experimenting with your script and I tried

var db_input = new DbConnection("odbc:DSN=xfODBC0");

where "xfODBC0" is the ODBC data source in Windows System ODBC setup. But it errors with
" Term 'result1.next' does not evaluate to a function." which I'm assuming means I don't have a valid connection.

Thanks,
Ron

[Aaron]

Here's an example showing how to connect to an ODBC data source in a script:

var db = new DbConnection("xdprovider=xdodbc;xddbtype=dsn;host=dsn_name;user id=uid;password=password");

[sprezzaturon]

Aaron, I've taken your example and created a simple script to just read the part number (Id_item field) and description (Descr_1 field) from the table, ITMMAS_BASE, in the database. But I'm not doing something correct because I get script runtime error: " Term 'result1.next' does not evaluate to a function. "

Here is the code snippet:

var db_input = new dbConnection("xdprovider=xfODBC;
xddbtype=dsn;
host=venture;
userid=[same as xfODBC property];
password=[same as xfODBC property]");
var db_output = HostApp.getDatabase();

var g_part_number_table = "/ITMMAS_BASE";
var g_part_number_field = "Id_item";
var g_part_desc_field = "Descr_1";

var g_part_numbers = new Array();
var result1 = db_input.execute
("
SELECT " +
g_part_number_field + ", " +
g_part_desc_field + " FROM " +
g_part_number_table + ";
");

while(result1.next())
{
var item = result1[g_part_number_field];
var desc = result1[g_part_desc_field];
g_part_numbers[item] = desc;
}
===============================================================
Here is a screenshot of the properties of ODBC data source:
http://screencast.com/t/LnscQdPiYS

Strata has no problems using the ODBC data source so I know I'm not setting something up correctly. What I have tried so far is:
1) xdprovider=xdodbc;
2) provider=xdodbc; and =xfODBC;
3) host=xfODBC;
4) dbtype=dsn;
5) combinations of the above.

Any ideas are greatly appreciated!
Ron

[Aaron]

Hi Ron,

Try removing the leading slash "/" from your tablename "/ITMMAS_BASE". In Strata, tables can be stored in folders, so when referencing tables in Strata, you can use the leading slash because it denotes that the table is in the root directory. However, in external databases that don't support folders, tables are referenced by their name without the slash. It's possible something else is going wrong, but let's try this first.

Best,
Aaron

[sprezzaturon]

Hi Aaron,

I'm sorry. I should have added the fact that I tried without the slash in front of the table name without any success. Do I have the dbConnection() parameters correct?
xdprovider=xfODBC; // xfODBC is the name of the data source that I created in ODBC admin.
xddbtype=dsn;
host=venture;

Thanks for your help,
Ron

[Ben]

Hi There,

Try this:

var db = new DbConnection("xdprovider=xdodbc;xddbtype=dsn;host=xfODBC;user id=[username];password=[password]");

The values for xdprovider and xddbtype should not be changed if you using a DSN-based odbc connection.

All the best,
Ben

[sprezzaturon]

Hello Ben,

I received the same error using "host=xfODBC". I also tried "user=[user shown in odbc admin]", "user id=", and "userid=" just in case I typed this parameter incorrectly.

Otherwise, Strata connects to my ODBC data source without any problems when I use "Create Connection" option.

Scripting should work with ODBC, yes?

Thanks,
Ron

[Ben]

Hello,

Ok, let's work through this. The first thing that we need to do is make sure the connection is good. To do this, let's insert the following code after your "new DbConnection" statement.

Code: Select all

if (db.isConnected()) alert("Connection succeeded");

If that works, then we'll work on the SQL statement.

All the best,
Ben

P.S. Yes, ODBC works with scripting

[sprezzaturon]

Hurray! I have a connection! So far so good...

[Ben]

Hurray! I have a connection! So far so good...

Ok, great! This means that probably something is wrong with your SQL statement. Can you do this and then paste the result here? The statement should be a valid SQL statement. If it isn't, then null will be returned. When trying to call the next() method on null, a runtime error is flagged. So, let's take a look at the actual SQL statement to review that it is good.

alert
("
SELECT " +
g_part_number_field + ", " +
g_part_desc_field + " FROM " +
g_part_number_table + ";
");

Best,
Ben

[sprezzaturon]

Good Morning, Ben,

The alert returns:
"SELECT Id_item, Descr_1 FROM ITMMAS_BASE;"

I verified that the table name and field name are correct.

Thanks,
Ron

[Ben]

Morning !

Let's try removing that semicolon at the end. What happens then? Some databases only accept the semicolons in their command-line interpreter (like Oracle with it's sqlplus).

All the best,
Ben

[sprezzaturon]

Without the ";" at the end, there is a pause of several seconds before I get the " Term 'result1.next' does not evaluate to a function. " error.

[Ben]

My last guess is that Synergy might have case-sensitive table or field names. Can you double-check the case of both the field names and the table names.

There are a couple of next steps that I'll let you know about. The first thing we can do is turn on ODBC logging and try to read what is causing the error.

The second step is for me to look for a Synergy developers kit so that I can determine why the query didn't work.

If the upper/lower case checks out on the fields and the table name, then I no longer think that the problem is syntactical in nature.

Thanks,
Ben

[sprezzaturon]

Hi Ben,

Yes, the case is exactly as I have obtained from our MIS department as well as from using names of the table and fields directly through Strata.

Thanks,
Ron

[Ben]

Hi Ron,

Ok. Just to make sure, can you try the following:

Code: Select all

var result = db.execute("SELECT * FROM ITMMAS_BASE");
if (result)
    alert("Succeeded!");
      else
    alert("Failed");


The next step is turning on ODBC logging to figure out what is causing the problem. The following steps describe this:

1. Go to Start, Control Panel
2. Open up Administrative Tools
3. Open up the Data Sources (ODBC) element
4. Go to the tracing tab.
5. Set the output file and click "Start Tracing Now"
6. Run your script (things should run more slowly because of the tracing)
7. Go to the same box as above and click "Stop Tracing"
8. You can post the file here (make sure there are no passwords, etc in the file) or send it to support [at] kirix [dot] com.

All the best,
Ben