How to total values where some are blank

[FM Tid]

I have the following situation :

For each record in the database, I have a repeating field "values" (up to 20 repetitions, 10 currently used), which shows the market value of this item from year to year. If, in a given year, the value has not changed, the repetition is blank, and the function Last is used to derive a value for use when totalling values across all records for the latest year.

So, a given record might look like this (where "Year" is simply a layout header text field, showing a list of years, while "Value" shows the contents of each repetition of the field "values")

Year

'99...'00...'01...'02...'03...'04...'05...'06...'07....'08

Value

25............30...........35....40............45....40

In the "total value" calculation, use of Last(values) returns 40 as the current value of this item (even though there is no value listed for 2008).

Now what I need to do is to produce a "total values" report not only for the latest year, but for each of the 10 years (and expandable into the future). If a value was present for each year, this would be an easy matter of using GetRepetition(values) for each repeating "values year" that was clicked.

However, you can see the problem, I think. GetRepetition(values) would return 0 for 2002, when it would need to return 30. Yet Last(values) would return 40, also incorrect.

Can someone give me a pointer how I could do this? Is there an existing function that will start from GetRepetition(n) and then work backwards from there to find the last non-blank value?

[comment]

I'm afraid you have made two rather basic mistakes: first, you have placed display before data. You have no data where there was no change, so one has to fill in the blanks. Instead, you should have a full data set for all years, and construct the display so that years with no change show as blank.

The second mistake is using repeating fields where a related table is called for. Repeating fields are not "expandable into the future", and it's difficult to produce reports from them.

[FM Tid]

Thank you for your reply, but there are no mistakes.

1. The production of values for a given year involves :

a) data input of catalogue values into a related file

: a Find of all records in the first file that have a related record in the second file for the latest year

c) a complex set of scripts and calculations run against the Found Set only, that determine "intermediate values" where an item does not correspond precisely with a catalogue value, in order to set a realistic value for the item

d) this operation entails many records NECESSARILY having blank values in some repetitions (nor do I apologise for placing emphasis on the importance of display)

All this was not a necessary part of the query I raised, but is a necessary response to your labelling it a "mistake".

2. You misunderstood what I meant about "expandable into the future". I was not talking about the "repeating field" attribute, which I do understand is no longer part of the FM vision. I was talking about this particular repeating field having 20 occurrences (repetitions) and therefore capable of a further 10 years of use/ data entry / reports.

I do welcome CONSTRUCTIVE criticisms and suggestions.

[Søren Dyhr]

Thank you for your reply, but there are no mistakes.

Yes there are indeed, do not use repeating fields for anything except very special cases, but as a rule never store user enterable data in them ... for multicriteria relations use instead pilcrow delimited textfields where only recordID's find their way to.

So Michael is dead right with his:

The second mistake is using repeating fields where a related table is called for.

So to be constructive ... simply ditch repeaters until are fully aware of how the relation mechanics works, and let you see how poor a substitute for genuine relations it really is to use repeaters....

--sd

[FM Tid]

Sigh, it seems every response I must make to my original query is to supply more and more details in explanation.

The EXISTENCE of the repeating field is straightforward. This database was started in 1994 in FM Pro 2, which was NOT a relational database. By the time I bought FM Pro 4 my database had already expanded beyond the point where it was worth while spending much time to completely redesign it from the ground up, and the repeating field I am referring to here was absolutely central to the raison d'être and functioning of the design.

I have since then used relational databases to expand it, as I mentioned in my previous reply. But not this particular field.

So to reiterate - the file was begun in FM2 and there is no mistake. So I would welcome how to solve my problem, given that it was started in the era - I repeat - BEFORE FM Pro became relational.

(This situation actually originates in FM Pro introducing "arrays" - which is what repeating fields actually are - without providing the programming methodology to fully support it.)

[comment]

So I would welcome how to solve my problem, given that it was started in the era - I repeat - BEFORE FM Pro became relational.

The way to solve your problem is to fix the two issues I have mentioned in my first post. I'm sorry if you took offense at my calling them "mistakes". The fact is that it doesn't matter what you call them, nor how or why they came to be. The important thing is that they are the reasons why you cannot produce the report you need.

[FM Tid]

Ok.

As it seems a difficult question, I am trying a home-grown solution of my own devising. I have ...

1. duplicated "values" and populated every repeat with the contents of "values" using a script (and I had to search here to discover why the use of Set Field with GetRepetition only worked with the first repeat). Every repeat of this copy now is filled with either values or 0.

2. I will be looking at how to loop through this array and if a value is 0, use the previous repeat instead, until every repeat is filled with non-0 value (again, using scripts).

3. As part of the annual processing in future, I shall program an additional script that sets up a value in the newest repeat being used. It should not be too difficult to do, though a little fiddly and irksome.

[comment]

It's not a difficult question at all. And I'm sure some "fiddly and irksome" workaround could be found to work with what you have. But the easy way to solve the problem (and additional problems which you are bound to run into in the future) is at its source. If you had a table with records like:

ItemID    Year     Value	

34        1999      25

34        2000      25

34        2001      30

34        2002      30

34        2003      35

34        2004      30

34        2005      40

34        2006      40

34        2007      45

34        2008      40

35        1999     102

35        2000     102

35        2001     109

...

producing the report would be a breeze, and displaying them the way you do now wouldn't be too difficult either - even in version 6.

[FM Tid]

I wouldn't dispute with you at all on that - and all files I have built from FM4 onwards (I skipped FM3) are built on relationship principles. But as I said before, this database was begun 15 years ago, and has many sophisticated interconnections with other databases I have in the same series, so in this one case it wasn't the answer.

I would also dispute everyone's apparent contempt here for repeating fields. Essentially, you are right in many BUT NOT ALL cases, for there are perfectly sound reasons for using arrays, which is what repeating fields can also be.

[comment]

I don't see your file, so I cannot judge. However, in my experience, it has ALWAYS been more efficient to fix structural flaws ONCE, rather than working around them EVERY TIME.

I would also dispute everyone's apparent contempt here for repeating fields.

I am afraid you are jumping to entirely unwarranted conclusions here:

http://fmforums.com/forum/showtopic.php?tid/190245/

[FM Tid]

That link didn't take me anywhere except Google (DNS not loading etc).

And I don't want to go on arguing for evermore in a pointless discussion, so I thank you for your efforts to help, and am bowing out. Goodnight.

[Vaughan]

That link pointed to a long discussion that celebrated all of the things that repeating fields are GOOD for.

A warning: there are some really bad puns early on.

[Søren Dyhr]

That link didn't take me anywhere except Google (DNS not loading etc).

If the tightness in your OpenDNS setting only gets you as long as Google, then use their mirror of it - in a Google search write:

site:www.fmforums.com "Post#267370"

...here choose the cache'ed version.

--sd

[Søren Dyhr]

All right we have encountered a solution of which could be said "If it ain't broke ...."

What then could be done is this (in the following do I only operate with 5 repetitions)

Sum( aRepeat ) +

Choose(

    Case( IsEmpty( GetRepetition( aRepeat; 2 ) ); 2 ^ 0; 0 ) +

    Case( IsEmpty( GetRepetition( aRepeat; 3 ) ); 2 ^ 1; 0 ) +

    Case( IsEmpty( GetRepetition( aRepeat; 4 ) ); 2 ^ 2; 0 ) +

    Case( IsEmpty( GetRepetition( aRepeat; 5 ) ); 2 ^ 3; 0 ) - 1;

    GetRepetition( aRepeat; 1 );

    GetRepetition( aRepeat; 2 );

    2 * GetRepetition( aRepeat; 1 );

    GetRepetition( aRepeat; 3 );

    GetRepetition( aRepeat; 1 ) + GetRepetition( aRepeat; 3 );

    2 * GetRepetition( aRepeat; 2 );

    3 * GetRepetition( aRepeat; 1 );

    GetRepetition( aRepeat; 4 );

    GetRepetition( aRepeat; 1 ) + GetRepetition( aRepeat; 4 );

    GetRepetition( aRepeat; 2 ) + GetRepetition( aRepeat; 4 );

    2 * GetRepetition( aRepeat; 1 ) + GetRepetition( aRepeat; 4 );

    2 * GetRepetition( aRepeat; 3 );

    GetRepetition( aRepeat; 1 ) + 2 * GetRepetition( aRepeat; 3 );

    3 * GetRepetition( aRepeat; 2 );

    4 * GetRepetition( aRepeat; 1 )

)

But a very important thing is that the first repetition not is left empty, otherwise would neither calc's nor scripts give any meaning at all ... since the logic then is flawed! Other ways to interpret this:

If, in a given year, the value has not changed, the repetition is blank, and the function Last is used to derive a value for use when totalling values across all records for the latest year

Seems far fetched, there must always be something ahead to tie the logic to???

--sd

[Lee Smith]

The same thing can be accomplished by typing #267370 in the [color:blue]Search above, in the Box above the word SEARCH.

Lee

Edited March 6, 2009 by Guest

[FM Tid]

Many thanks for these helpful suggestions. (The reason for leaving some repeats blank - I could equally have set them to 0 and used "Do not display number if 0" - was that the presence of a particular "value repeat" depended on there being a related "values record" in a separate table; if there was no related record, there was no change in value, therefore no value repeat to calculate. Plus, it just LOOKED tidier also).

In fact, I went away and gave the matter some thought, and after discovering that Set Field and (GetRepetition+variable used as pointer) do not work together to MODIFY a repeat (only to fetch or test it), I came up with this solution

Having defined a duplicate array of the values array, and used Set Field and GetRepetition to populate it from the original array, I then went through the new array starting from the 2nd repeat, testing each repeat with the following logic :

If repeat is 0, Set Field (repeat) (repeat -1)

which solved the problem. I now have an array which I can use to provide summary totals for each of the years, but still use the first array to display on the screen. I also have a couple of scripts to run in future years, when I use yet another of the array's repeats. (And this is also recorded in a Tutorial layout, which is something I cannot recommend highly enough to anyone who has devised a complex database and might tend to forget things!)