Relationship using repeating field

[Bertie]

Hi all

 

I have an issue which I'm hoping is easy to resolve. I have 2 tables, Plots & Stages. There are many Stage records related to the plots table.  I would like to display the related stage records in a horizontal row under a Stage header.

 

In my Plot record I have a unique id and I was hoping to use a repeating calculation field, get(calculationrepetitionnumber) with 50 repeats which gives me a repeating field with values 1 through 50.

 

In my Stage table I have the plot id, a stage value ( a value from 1 to 50 ) and a data field.

 

I was hoping to join the tables by both the plot Id and then the  repeating field to the stage value and then display the data in the plot table in another repeating field which equals the data result of the relationship.

 

This works in my head, not in reality.

 

I've attached a demo as it is much easier than I've made it sound.

 

Any help much appreciated.

 

RepeatRelationship.zip

[bcooney]

Bertie,

 

As tempting as FM is to dive right in, you'll save yourself aggravation and time redoing work if you understand how relationships are constructed. I've attached a demo for you. You'll see that based on your description, each Plot record can be related to many Stage records. To show that, you'd use a portal on the Plot form layout to show/create the related stage records.

 

User's Guide: https://fmhelp.filemaker.com/docs/13/en/fmp13_users_guide.pdf

 

hth,

Barbara

Bertie.fmp12.zip

[Bertie]

Bertie,

 

As tempting as FM is to dive right in, you'll save yourself aggravation and time redoing work if you understand how relationships are constructed. I've attached a demo for you. You'll see that based on your description, each Plot record can be related to many Stage records. To show that, you'd use a portal on the Plot form layout to show/create the related stage records.

 

User's Guide: https://fmhelp.filemaker.com/docs/13/en/fmp13_users_guide.pdf

 

hth,

Barbara

Hi Barbara thanks for your reply.

 

 I explicitly need all my stages showing horizontally under the appropriate columns. 1 line per plot. I can't see how to do this unless I create a relationship for each column ( there are 50!).

 

You solution would give a list of up to 50 stages for the first plot then another list for the 2nd plot?

[comment]

 I explicitly need all my stages showing horizontally under the appropriate columns. 1 line per plot. I can't see how to do this unless I create a relationship for each column ( there are 50!).

 

You can do this by placing 50 one-row portals (all based on the same basic relationship) side-by-side.

 

If not all Plots have 50 related Stages and you want specific stages to show up in specific columns, then filter the portals to show only Stages of specific type each. Otherwise just set each portal to start at a different row.

 

I am not sure how many Plots you want to show at the same time, but don't expect this to be fast, no matter which technique you end up adopting (unless you denormalize your data by populating a repeating field (or fields) in the Plots table via a script).

 

 

---

P.S. Technically, you could also define 50 distinct relationships between Plots and Stages, with each relationship "seeing" only Stages of a specific type. But in order to implement this, you would also need to define 50 distinct Type fields in the Plots table. A repeating field with 50 repetitions could not be used for this, because a repeating field is the equivalent of a multi-key field when used as a matchfield in a relationship.

Edited March 17, 2015 by comment

[Bertie]

You can do this by placing 50 one-row portals (all based on the same basic relationship) side-by-side.

 

If not all Plots have 50 related Stages and you want specific stages to show up in specific columns, then filter the portals to show only Stages of specific type each. Otherwise just set each portal to start at a different row.

 

I am not sure how many Plots you want to show at the same time, but don't expect this to be fast, no matter which technique you end up adopting (unless you denormalize your data by populating a repeating field (or fields) in the Plots table via a script).

 

Thanks for your reply

 

Filtering the portal to show only the stage I want in each column does do exactly what I want. I did think about populating the data into repeating fields but it needs to be editable therefore I'd need a reverse script to set the data back once the edits were complete, it all seemed a bit like hard work ( not to mention slow ).

 

Bearing in mind your concerns regarding speed, is there another technique I should be thinking of looking at?

 

many thanks 

[comment]

I did think about populating the data into repeating fields but it needs to be editable therefore I'd need a reverse script to set the data back once the edits were complete, it all seemed a bit like hard work ( not to mention slow ).

 

Yes, it would take some work, but it would be the fastest one to display. Though 50 columns times (how many?) rows may take some time to render - even if all the data comes from stored, local fields.

 

BTW, there's also a limit to what the human eye can perceive when looking at a 50 x ? grid.