Related value list nightmare

[eddyb2]

Hi

Really stuggling with this, please help!

I have 3 tables

BASE

SECTOR

SELECTION

Within BASE I have fields

Code

Sector (selectecd from a value list based on all records in SECTOR)

Within SECTOR I have fields

Sector

List (calculated field - lists data from several other fields)

Within SELECTION I have fields

Code

Sector

Selection1

Relationships...

SELECTION is related to BASE by the code field on both sides

This relationship provides the sector field in SELECTION

SELECTION is then related to the SECTOR table

SELECTION:sector = SECTOR:sector

Hope that makes sense so far !!

Now what I want to do is with the field Selection1 in the SELECTION table is to display a vlaue list that brings back the contents of the list field from the SECTOT table based on the relationship SELECTION:sector=SECTOR:sector

But I just cant seem to get this to work

I wither get nothing back in the valuelist or I get the contents of "list" for every record in the SECTOR table

Please can someone help me !!

Thanks in advance

Ed

[eddyb2]

Hi all,

Can anyone help me out with this please.

If I need to supply more details please let me know.

Many thanks

Ed

[mr_vodka]

List (calculated field - lists data from several other fields)

What is EXACTLY in this field?

[eddyb2]

Thanks for your reply.

The List Field is a calcluated result from 30 other fields and some additional text

List (

"01- " & Trim(SL1C1 & " " & SL2C1 & " " & SL3C1);

"02- " & Trim(SL1C2 & " " & SL2C2 & " " & SL3C2);

"03- " & Trim(SL1C3 & " " & SL2C3 & " " & SL3C3);

"04- " & Trim(SL1C4 & " " & SL2C4 & " " & SL3C4);

"05- " & Trim(SL1C5 & " " & SL2C5 & " " & SL3C5);

"06- " & Trim(SL1C6 & " " & SL2C6 & " " & SL3C6);

"07- " & Trim(SL1C7 & " " & SL2C7 & " " & SL3C7);

"08- " & Trim(SL1C8 & " " & SL2C8 & " " & SL3C8);

"09- " & Trim(SL1C9 & " " & SL2C9 & " " & SL3C9);

"10- " & Trim(SL1C10 & " " & SL2C10 & " " & SL3C10))

Thanks

[Søren Dyhr]

Such a statement with finite number of iterations cries out for the use of repeating calc' fields ... or if it gets infinite this:

http://www.briandunning.com/cf/868

But it doesn't seem particular normalized when you say this:

The List Field is a calcluated result from 30 other fields and some additional text

Dare I say a pretty spreadsheet'ish method?

--sd

[LaRetta]

Now what I want to do is with the field Selection1 in the SELECTION table is to display a vlaue list that brings back the contents of the list field from the SECTOT table based on the relationship SELECTION:sector=SECTOR:s ector

Eddy, whenever I see questions such as this, and then see the calculation you present with multiple fields such as SL1C1-10, SL2C1-10 and so forth, I can only believe that your structure is not properly normalized.

You should never have to go through these types of complexities particularly for value lists when conditional value lists are possible under a good structure. I would suggest that you reconsider your design at this point to save yourself many further headaches.

LaRetta :wink2:

[Søren Dyhr]

I sometimes curse the unique filemaker implantation of calc'fields ...while admitting that event triggers script sequential execution, might be quite a handful to manage.

--sd

[eddyb2]

Thanks for your replies but due to the way this solution is to be used I do not see any other option. Maybe I should explain briefly.

I have a series of users all using very advanced spreasdsheets - this is a must and there is no option of moving a way from this into FileMaker.

To get the data out of these spreadsheets I have setup scripts in Excel that creat an ADO connection using the FileMaker ODBC connector.

The scripts input data from the spreadsheet into FileMaker.

This creats records in the SECTOR database

But - it only takes a cell and puts this into a field.

So what this is actually doing is taking headers - which in the Excel spreadsheet are over 3 rows

So say we have 10 columns row1, 2 and 3 are filled with headings for the data that comes below

So what I am doing with the list field is taking the heading line 1, heading line 2 and heading line 3 and making these into 1 line.

These lines of headings then need to become available in a different table as a value list - my final goal!

If you can see a better way of doing it I am certainly open to any and all suggestions as I too am not happy about the structure but needs must...

Thanks

[Søren Dyhr]

The scripts input data from the spreadsheet into FileMaker.

If it only can make one single record, should you attempt to do it opposite, via a filemaker script, that makes individual records from the plucked data ... well I do not quite know if this makes sense, since I havn't been involved with ODBC'ing ever since fm5.5, where filemaker not could be regarded as a properly SQL implemented backbone yet, there were a lot circumventions back then, to make things happen and oftentimes via setting values trigging events in the opposite end.

I do however find it very important to break data into normalized structure, to prevent massive calc's ... perhaps someone will chime in with fresh experience here??

--sd

[eddyb2]

It can make multiple records but can only take contents of one excel cell and enter this into 1 field at a time - thats the issue I cannot join the headers before passing them to the database.

In my list field I am them joining the 3 cells to make the 1 header- the list of headers is then to be the value list in another table

The list itself works well- although it not being the neatest solution.

The list is then available as a value list in the current table but I am stuggling making htis available in a related table

Thanks again

Edited November 5, 2008 by Guest

[eddyb2]

Can anyone provide any other suggestions for this problem. I am still struggling to get this to work

Many thanks for any help that you can give

[Oldsneekers]

I'm looking at your relationships:

BASE.............SELECTION............. SECTOR

code======code..................... listCc

.................... sector========sector

.................... selection1

This should work if 1) SECTOR:sector is indexed and 2) SECTOR:listCc the calculation field is indexed but if references other fields that are not or cannot be indexed because it is itself based on another relationship, it probably won't work.

good luck

PS ignore the periods in the above "table" they're used to make the diagram line up for visual purposes only.

Edited November 13, 2008 by Guest