Simple Relationship Question - Based on Length()

[deego55]

I have a table with thousands of records, which have identifiers of various length. i want to create another table occurance that only shows a subset of the records, based on the length of the identifier.

I created a Length(ID) field and another stored caculation field with the desired length (e.g. 17). Both are set as number results.

i tried creating a relationship between the two tables with these fields, but it is pulling all of the records in, not just the ones with the IDs that are 17 characters.

Am i missing something? thanks!

[Fenton]

The thing is, you've got 17 in a number field, on the originating side of a relationship, targeting the Length field (=)?. But that relationship is only going to work when the length of the current record is 17.

If you want to find all the 17 length records in the database, then you need a "constant" or global field with 17 in it, the same for all records.

Two ways to do this, two different types of fields. If it's always (or nearly always) going to be 17, then you create a calculation field, result number, =17, Storage options [x] Do not store (though it doesn't really matter, as it's so small).

Or set a number field with global storage to 17, via script, and Commit Record. Global values are only temporary on a networked client, so it would need to be reset each time you want to use it, hence scripted. The value on the host is persistent, it is whatever it last was as a single-user file; this can be a problem if you want to change it, hence scripted.

Use that on the originating side of the relationship.

[deego55]

hi fenton. thanks for the response.

i did create a calculation constant with "17". i did a match with length(id) on the original table = constant in the secondary table.

if i view a layout based on the second table and display both fields, i can see that the 17 constant is set for every record, but all records are showing up, not just those with the length of 17.

i thought that was all i would need to do, but it doesnt seem to work.

[David Jondreau]

You've got your relationship backwards. Or rather the layout tables backwards. The layout needs to be based on the table that uses the constant in the relationship.

Think about it. You're sitting on a set of records. The one you're looking at has a Length(ID) of 5. Your relationship to another table is find all Constants that equal 17. You find nothing. Go to a a record with a Length(ID) of 17 and you'll see ALL the records of the related table because they all meet the criteria.

[deego55]

Hi DJ. the layout is based on the second table, which is using the constant. here is my setup:

TABLE 1

id

length (id)

calc constant = "17"

Then I created a second table occurance, TABLE 2

RELATIONSHIP

TABLE 1::length (id) = TABLE 2::calc constant

Then i go to layout based on TABLE 2, but i still see all records from table 1. i was expecting to see only those with a id length of 17.

sorry for my lack of understanding. thanks for the help.

[comment]

To see only specific records, you need to modify the found set - either by finding, or by performing a Go to Related Record[] script step. Changing a layout by itself isn't going to do it*. Another option is to view the records in a portal - this will always show only related records.

---

(*) This seems to be a very popular misconception in the last day or two:

http://www.fmforums.com/forum/showtopic.php?tid/192993/post/280190/#280190

http://www.fmforums.com/forum/showtopic.php?tid/193006/post/280280/#280280

[David Jondreau]

All layouts are based on table occurrences and no TO filters out records by itself. You need to 'traverse' the relationship in order to use it, which can be done via the Go To Related Record script step.

But what are you trying to accomplish? Finding a subset of records can easily be done with a "Find".

[deego55]

Thanks all. i understand now.

the goal is to parse out the records into multiple tables. i get an import file that has various WBS levels in it, which can be determined by the ID length. they are all loaded into one table.

i want to then take that table and load the appropriate records into the correct WBS level tables, based on their ID.

i was thinking that i could easily do this by using self joins based on the id length.

i suppose i can just do a looping find script and copy the records.

[David Jondreau]

You can import the records from the import table to the WBS level. Only the records of the found set of the source table are imported.

[comment]

the goal is to parse out the records into multiple tables.

What will be achieved by that? IOW, what will you be able to do then that isn't possible while the records are in a single table?

[deego55]

comment - i need to run a lot of validations against the records. many of them have to do with checking for related lower level WBS records, and if there are none identified, create the appropriate record(s). this was easiest for me to achieve when the data is in seperate tables.

also there are validations that i need to check that are only applicable to a certain WBS level. it seemed easier to me to run these against just those records, with them being in a seperate table.

also, there is going to be instances where i will need to change some of the data in the records, but i want to retain the original data, so i can see what was changed.

it may be possible to achieve all in one table, but im not sure if i know how. i dont have a ton of experience. would it be self joins for each level?

you are all a great help though, i appreciate it.

Edited February 4, 2008 by Guest

[comment]

Obviously I don't understand these requirements, but it seems that doing a find first, then operating on the found set could achieve the same thing - with significantly less resources. As for calculations/validations, they can be branched using Case() or Choose().

[David Jondreau]

My (limited) understanding of WBS is that a recursive structure would be the ideal way to go, but that may be beyond the scope of the OP's mission.

Here's two wortwhile articles:

http://www.excelisys.com/web/downloads/index.php

http://jonathanstark.com/recursive_data_structures.php