Yes, both of these are possible. The relationship one is a little ugly, but still possible.
1) To create the relationship, create a field that has a list of all possible IDs in it. For example:
Use that as your "parent" (primary) key field. The "child" (foreign) key is the ID in the targeted table.
2) Your ExecuteSQL statement is considerably easier:
SELECT SUM ( [field you need ] ) FROM ( [ child table ] ) WHERE [ idField ] BETWEEN 1000 AND 3999 OR [ idField ] BETWEEN 5000 AND 5999
(At least I think that's right. If not, someone smarter than I am on SQL will correct me.)
Thanks for your input Mike,
Actually I did think of your first and "ugly" solution, but had hoped there might be a better one. In my soultion the user has to define these intervals in a field. It is for a user definable report. So when I run this report, I would have some kind of script to begin with to establish this relation.
I guess the SQL solution will be more elegant - only problem - I know to little about constructing this query.
So the SQL should find from the child tables these records
records in date interval xx..xx with ids in range 1000..3999 and another field should be empty
records in date interval xx..xx with ids in range 5000.5999 and another field should be empty
+ eventually more intervals.
If I at least could get just one Query done - I would be able to loop through the different intervals and calculate the result via variables.
So if somebody could help to construct this SQL - I would be very happy.
I'm not sure if I got you right but wouldn't a relation with bolean-formula work?
Ad global-fields "from" and "to" to yours invoice(?)-table:
invoice::from ≥ invoiceSUM::ID
invoice::to ≤ invoiceSUM::ID
I wonder how FM acts when you use repeating-fields definition for field "from" and "to" *).
from= 1'000 | 5'000
to = 30999 | 5'999
*) be aware that if this works it might be a shaky solution and might fail in the future with next FM updates
Great idea. Unfortunately, it doesn't work. (See attached file.) Bummer, because it would be really cool if it did.
However, it did trigger another thought. You could follow the same logic and use a series of start / end globals, like this:
and then use a multi-predicate relationship to pull it. Might be more than a pain than it's worth; you'd have to add more fields if you needed additional ranges, but it also could end up being easier if you wanted to make the ranges more dynamic.
Thanks! Good contribution!
boolTest.fmp12.zip 16.4 K
You could try something like this:
SELECT SUM ( [field you need ] ) FROM ( [ child table ] ) WHERE ( [ idField ] BETWEEN 1000 AND 3999 OR [ idField ] BETWEEN 5000 AND 5999 ) AND [ field that shouldn't be there ] IS NULL
See how that works for you.
I would give it another try:
the fields 'from' and 'to' must be set to indexed "global"!
It works at least without that repeating-field trick.
Though only 1 argument for 'from' and 'to' will work.
from = 1 | 100
to = 200 | 300
shows results from 1 to 300
The relation-graph allows only AND arguments between the statements:
from ≥ ID
AND to ≤ ID
I have posted a feature-request long ago to expand this with further arguments like OR
You would need this and a feature to set parantheses in arguments to get done:
( from_a ≥ ID
AND to_a ≤ ID )
OR ( from_b ≥ ID
AND to_b ≤ ID )
But you can use that relation (without repeating-fields) in a script-loop to set an index (field Mark = 1) and have another relations 1=1
No, sorry. Still doesn't work. Changing the storage to global makes no difference.
But an "OR" relational join would indeed be nice.