Extend over extended?

[normanicus]

Hello,

If I do this in a calculation field with 12 repetitions:

GetNthRecord ( Extend( Analysis::Month Total ) ; 13 - Get ( CalculationRepetitionNumber ) )

it works. I get my 12 repetitions with values.

As soon as I add any logic to the calculation as here:

Case (

Source = "West" ; GetNthRecord ( Extend( Analysis::Month Total ) ; 13 - Get ( CalculationRepetitionNumber ) )

)

only the first repetition has a value in it.

Is there any way round this?

Thanks for any help possible,

Norman

[Raybaudi]

Case (

Extend ( Source ) = "West" ; GetNthRecord ( Extend( Analysis::Month Total ) ; 13 - Get ( CalculationRepetitionNumb er ) )

)

[normanicus]

I'm sorry, that doesn't work.

[normanicus]

But with the idea you gave I understood that I had to use Extend() on every field involved. That solved it for me. Thank you very much. With that information I could do this:

Let (

[

ExtSource = Extend ( Source ) ;

Reps = Get ( CalculationRepetitionNumber )

] ;

Case (

Extend ( Data Source ) = "Hire" ;

Case (

ExtSource = "West" ; GetNthRecord ( Extend( Analysis::Month Total ) ; Reps ) ;

ExtSource = "Express" ; GetNthRecord ( Extend( Analysis::Hire Month Total Express) ; Reps ) ;

ExtSource = "North" ; GetNthRecord ( Extend( Analysis::Hire Month Total North ) ; Reps ) ;

ExtSource = "South West" ; GetNthRecord ( Extend( Analysis::Hire Month Total South West ) ; Reps ) ;

ExtSource = "Cream" ; GetNthRecord ( Extend( Analysis::Cream Month Total ) ; Reps ) ;

GetNthRecord ( Extend( Analysis::Hire Month Total ) ;Reps )

) ;

Extend ( Data Source ) = "Sales" ;

Case (

ExtSource = "West" ; GetNthRecord ( Extend( Analysis::Sales West Month Total ) ; Reps ) ;

ExtSource = "Express" ; GetNthRecord ( Extend( Analysis::Sales Express Month Total ) ; Reps ) ;

ExtSource = "North" ; GetNthRecord ( Extend( Analysis::Sales North Month Total ) ; Reps ) ;

ExtSource = "South West" ; GetNthRecord ( Extend( Analysis::Sales South West Month Total ) ; Reps ) ;

ExtSource = "Cream" ; GetNthRecord ( Extend( Analysis::Cream Month Total ) ; Reps ) ;

GetNthRecord ( Extend( Analysis::Sales Month Total ) ; Reps )

) ;

Extend ( Data Source ) = "Combined" ;

Case (

IsEmpty ( Source ) ; LeftValues ( GetNthRecord ( Extend( Analysis::Month Total ); 12) ; Reps ) ;

ExtSource = "West" ; GetNthRecord ( Extend( Analysis::Month Total West ) ; Reps ) ;

ExtSource = "Express" ; GetNthRecord ( Extend( Analysis::Month Total Express ) ; Reps ) ;

ExtSource = "North" ; GetNthRecord ( Extend( Analysis::Month Total North ) ; Reps ) ;

ExtSource = "South West" ; GetNthRecord ( Extend( Analysis::Month Total South West ) ; Reps ) ;

ExtSource = "Cream" ; GetNthRecord ( Extend( Analysis::Month Total Cream) ; Reps )

)

)

)

Which I only show to demonstrate to others with the problem how all the referenced fields must be extended.

Thanks again,

Norman

PS It appears there is a limit to word length here so ...Number is getting split to ...Numb er when it is displayed.

[normanicus]

Apologies once again. Using variables allowed the calculation to resolve but produced incorrect results. So the calculation I showed becomes even more verbose:

Case (

Extend ( Data Source ) = "Hire" ;

Case (

Extend ( Source ) = "West" ; GetNthRecord ( Extend( Analysis::Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "Express" ; GetNthRecord ( Extend( Analysis::Hire Month Total Express) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "North" ; GetNthRecord ( Extend( Analysis::Hire Month Total North ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "South West" ; GetNthRecord ( Extend( Analysis::Hire Month Total South West ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "Cream" ; GetNthRecord ( Extend( Analysis::Cream Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

GetNthRecord ( Extend( Analysis::Hire Month Total ) ; Get ( CalculationRepetitionNumber ) )

) ;

Extend ( Data Source ) = "Sales" ;

Case (

Extend ( Source ) = "West" ; GetNthRecord ( Extend( Analysis::Sales West Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "Express" ; GetNthRecord ( Extend( Analysis::Sales Express Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "North" ; GetNthRecord ( Extend( Analysis::Sales North Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "South West" ; GetNthRecord ( Extend( Analysis::Sales South West Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "Cream" ; GetNthRecord ( Extend( Analysis::Cream Month Total ) ; Get ( CalculationRepetitionNumber ) ) ;

GetNthRecord ( Extend( Analysis::Sales Month Total ) ; Get ( CalculationRepetitionNumber ) )

) ;

Extend ( Data Source ) = "Combined" ;

Case (

IsEmpty ( Source ) ; LeftValues ( GetNthRecord ( Extend( Analysis::Month Total ); 12) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "West" ; GetNthRecord ( Extend( Analysis::Month Total West ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "Express" ; GetNthRecord ( Extend( Analysis::Month Total Express ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "North" ; GetNthRecord ( Extend( Analysis::Month Total North ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "South West" ; GetNthRecord ( Extend( Analysis::Month Total South West ) ; Get ( CalculationRepetitionNumber ) ) ;

Extend ( Source ) = "Cream" ; GetNthRecord ( Extend( Analysis::Month Total Cream) ; Get ( CalculationRepetitionNumber ) )

)

)

But I think it is now working OK.

[Søren Dyhr]

Why isn't this solved via multi criteria relations instead of an extensive Case( structure, I once made huge price matrix that way ... became slow as molasses.

I've BTW made you a suggestion to get rid of some of the repeaters, not that they're bothering you in any way of course

--sd

Graphing.zip

[LaRetta]

:jester:

Soren, you are too cute!

I agree 100% this time ... ditch the repeaters or they will continually haunt you, as you are finding out.

Edited January 15, 2008 by Guest

[normanicus]

If someone can tell me how to get data from many relationships into a Web Viewer at the click of a button then I will happily abandon these repeaters. I have always avoided them like the plague. However, if one is representing months of the year then there are always 12 so one of the objection goes. That big calculation allows me to pull data from many different relationships dynamically when various drop-down values are chosen. This allows me to use just one Web Viewer in one layout.

What I am doing is based on Fusion Charts Free for Filemaker. Great looking charts fro free but, as always, adapting a tool to client needs is an involved process,

Norman

[Søren Dyhr]

What I tried to get thru you in my template is that your data, more or less have the same attributes, so your idea of having them in separate tables is entity duplication, which is inconvenient - instead should merge them, and break the attributable differences out in new one-2-one relations if required.

The classic eaxample here is present students vs present dito ... one or two tables??

Why does it happen? because you give your table misleading names, sales and aqusistions goes into one single table called say transactions, here are the attributal difference solved via the sign in the figure, but often is it broken out in a field.

--sd

[LaRetta]

Hi Norman,

I confess that I too made assumptions and I shouldn't have. But any time I see something with this kind of pattern (skip all the calc-speak for a moment), it screams 'there's a better way':

"Hire" ; "[color:green]West" ; Get Month Total [color:green]West ;

"Hire" ; "[color:green]Express" ; Get Month Total [color:green]Express ;

"Hire" ; "[color:green]North" ; Get Month Total [color:green]North;

"Hire" ; "[color:green]South West" ; Get Month Total [color:green]South West ;

"Hire" ; "[color:green]Cream" ; Get Month Total [color:green]Cream;

"Sales" ; "[color:green]West" ; Get Month Total Sales [color:green]West;

"Sales" ; "[color:green]Express" ; Get Month Total Sales [color:green]Express;

"Sales" ; "[color:green]North" ; Get Month Total Sales [color:green]North;

"Sales" ; "[color:green]South West" ; Get Month Total Sales [color:green]South West;

"Sales" ; "[color:green]Cream" ; Get Month Total Sales [color:green]Cream;

"Combined" ; "[color:green]West" ; Get Month Total Sales [color:green]West;

"Combined" ; "[color:green]Express" ; Get Month Total Sales [color:green]Express;

"Combined" ; "[color:green]North" ; Get Month Total Sales [color:green]North;

"Combined" ; "[color:green]South West" ; Get Month Total Sales [color:green]South West;

"Combined" ; "[color:green]Cream" ; Get Month Total Sales [color:green]Cream

... it tells me you are working waaayyy too hard. As Soren suggests, you have the 'same attributes' so they shouldn't be in separate tables and there would then be no need to gather them back up for display in web viewer. But we would need to see your file (or a good representation) so we don't *mis-guess.

The longer you wait, the harder it will be to change, just like in most life-things. I don't always agree with Soren, which I'm sure he points to and says, "Whew! That's a relief" but this one is worth working through, I think.

* mis-guess. That strikes me as very funny because how can a guess be wrong?!

LaRetta

Edited January 16, 2008 by Guest