Hi there,

I wonder if there is a way to automatically determine the quota/number from a range of numbers (almost certainly, but I can't figure it out - luckily, there are smarter brains out there, perhaps you?). As this might sound enigmatic or just gibberish, I'll try to illustrate it with my example:

The fields "Year_start" and "Year_end" contain numeric values describing the date range of archaeological objects (coins). The goal is to automatically process the data in order to get a table showing the quota (=probability of manufacturing) per year for all the data sets, e.g.

Coin 1 | Coin 2 | |
---|---|---|

Year_start | 1 | 8 |

Year_end | 10 | 15 |

Years | Coin 1 | Coin 2 | Total |
---|---|---|---|

1 | 1/10 (= decimal value) | 1/10 | |

2 | 1/10 | 1/10 | |

3 | 1/10 | 1/10 | |

4 | 1/10 | 1/10 | |

5 | 1/10 | 1/10 | |

6 | 1/10 | 1/10 | |

7 | 1/10 | 1/10 | |

8 | 1/10 | 1/8 | 1/10+1/8 |

9 | 1/10 | 1/8 | 1/10+1/8 |

10 | 1/10 | 1/8 | 1/10+1/8 |

11 | 1/8 | 1/8 | |

12 | 1/8 | 1/8 | |

13 | 1/8 | 1/8 | |

14 | 1/8 | 1/8 | |

15 | 1/8 | 1/8 |

Adding the values for each year to get the total, and the basic mathematical formula to obtain the quota/year "=1/(Year_start - Year_start+1)" are clear to me. However, I am struggling with the work in-between...

Thank you very much for your help!

------

FileMaker 12 Pro (Windows)

So, your first table is the input and the second table is what you want as a result?

Coin1, Coin2... How many?

You could do this by using GetSummary from Minimum and Maximum Summary fields on each coins,

or just script through each sorted field looking for the absolute earliest Year_start and latest Year_end,

putting those into $yearStartAll and $yearEndAll variables.

Then you can have a loop that starts Year with $yearStartAll and Exit Loop If [ $year > $yearEndAll ],

which makes a New Record on each iteration,

and populates Year with the iterated $year.

You can collect each $yearStartCoin and $yearEndCoin either

while collecting $yearStartAll and $yearEndAll,

or in a second pass.

Use $yearStartCoin and $yearEndCoin to calculate your fractions for the coins

and to determine where to populate those fractions as you loop through the records.

There are custom functions and other methods that could simplify some of this,

but this might be the easiest way to think about what needs to be done.