Kirix Support Forums

relationship between 2 tables

Please post any help questions, requests or other feedback here.

relationship between 2 tables

Postby sprezzaturon on Wed Jul 16, 2008 11:47 am

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Aaron on Wed Jul 16, 2008 4:21 pm

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
screenshot1.png (39.52 KiB) Viewed 6924 times


Then, setting up the relationships, we have:

screenshot2.png
screenshot2.png (29.38 KiB) Viewed 6924 times


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

screenshot3.png
screenshot3.png (41.58 KiB) Viewed 6923 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
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: relationship between 2 tables

Postby sprezzaturon on Wed Jul 23, 2008 7:41 am

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Aaron on Wed Jul 23, 2008 9:57 am

No, if the tables are copied, the data in the copied tables doesn't update when the data in the original tables change.
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: relationship between 2 tables

Postby sprezzaturon on Wed Jul 23, 2008 10:26 am

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?
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Aaron on Wed Jul 23, 2008 2:26 pm

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.
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: relationship between 2 tables

Postby sprezzaturon on Thu Jul 24, 2008 6:56 am

Yes please, Aaron, I would greatly appreciate learning about that script.
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Aaron on Thu Jul 24, 2008 5:44 pm

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
Attachments
script_example.zip
(3.24 KiB) Downloaded 307 times
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: relationship between 2 tables

Postby sprezzaturon on Fri Jul 25, 2008 1:43 pm

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Aaron on Fri Jul 25, 2008 9:50 pm

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");
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: relationship between 2 tables

Postby sprezzaturon on Wed Jul 30, 2008 12:31 pm

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:
Image 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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Aaron on Wed Jul 30, 2008 2:18 pm

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
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: relationship between 2 tables

Postby sprezzaturon on Fri Aug 01, 2008 10:29 am

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Ben on Fri Aug 01, 2008 4:29 pm

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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Re: relationship between 2 tables

Postby sprezzaturon on Mon Aug 04, 2008 7:02 am

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Ben on Mon Aug 04, 2008 12:25 pm

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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Re: relationship between 2 tables

Postby sprezzaturon on Tue Aug 05, 2008 6:00 am

Hurray! I have a connection! So far so good...
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Ben on Tue Aug 05, 2008 3:35 pm

sprezzaturon wrote: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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Re: relationship between 2 tables

Postby sprezzaturon on Wed Aug 06, 2008 6:58 am

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Ben on Wed Aug 06, 2008 9:14 am

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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Re: relationship between 2 tables

Postby sprezzaturon on Wed Aug 06, 2008 10:02 am

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.
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Ben on Wed Aug 06, 2008 10:23 am

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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Re: relationship between 2 tables

Postby sprezzaturon on Wed Aug 06, 2008 12:42 pm

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
sprezzaturon
Registered User
 
Posts: 21
Joined: Tue Feb 05, 2008 9:12 am

Re: relationship between 2 tables

Postby Ben on Wed Aug 06, 2008 2:01 pm

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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Return to Strata Help & Feedback