12 Replies Latest reply on May 9, 2013 7:58 AM by user17753

# Add fields based on criteria of records

I am trying to come up with a way to sum a field in a record set based on the following:

f_Date f_Time f_number

1/1/2013 01:00 100

1/1/2013 02:00 125

1/1/2013 03:00 75

1/1/2013 04:00 150

What I would like to do is to add the numbers in the f_number field based on the f_Time field. If the f_Time field is between 01:00 and 12:00 add the numbers in f_number field. If the f_Time field is between 01:00 and 17:00 add the numbers in the f_field then if the f_Time field is between 01:00 and 24:00 add the numbers in the f_field.

The records in the f_Time field are from 01:00 to 24:00 as in 24 hours in a day.

The date is duplicated in each record to facilitate someone exporting to Excel and be able to manulipate data better. I have set it up as a flat database but I am certianly open to setting it up differently.

Any help anyone could provide would be greatly appreciated as I seemed to be stumped.

• ###### 1. Re: Add fields based on criteria of records

So if I understand the question correctly, you want three sums:

1) Total of f_number for 01:00 <= f_Time <= 12:00

2) Total of f_number for 01:00 <= f_Time <= 17:00

3) Total of f_number for 01:00 <= f_Time <= 24:00

presumably for a given date, yes?

If that's correct, there are a few different ways to do it. You can:

1) Set up three self-joining relationships, with hard-calculated fields for the boundary times and using the date field on the current record. The number total then equals Sum ( selfJoin::f_number ).

2) Set up a single self-joining relationship, using a pair of global time fields for the boundary times. This is easier on your relationship graph, but requires you to populate the global fields (such as via script) in order to generate the values.

3) Use ExecuteSQL ( ) to create your totals. This is probably my preferred method, but requires writing a little SQL code. Example:

ExecuteSQL ( "SELECT SUM ( f_number ) FROM table WHERE f_Date = ? and f_Time BETWEEN 01:00 AND 12:00" ; "" ; "" ; f_Date )

(You might have to play with the time formats a bit; not sure how ExecuteSQL parses time.)

HTH

Mike

• ###### 2. Re: Add fields based on criteria of records

I like the idea of the three self joining relationships. This is because the people that will be entering this data will have the program open 24 hours per day.

When I self-join, I have joined the f-Date - f_Date and f_Time - f_Time for two different self join relationships. Does this sound correct?

• ###### 3. Re: Add fields based on criteria of records

What you'll need to do is define four calculation fields on the parent side to serve as keys:

oneAM = GetAsTime ( "01:00" ) (time)

noon = GetAsTime ( "12:00" ) (time)

fivePM = GetAsTime ( "17:00" ) (time)

midnight = GetAsTime ( "24:00" ) (time)

Then, you're going to create relationships based on multiple criteria:

Date = Date

oneAM <= f_Time

noon >= f_Time

This will give you all records between 1:00 and 12:00 on the date referenced in the current record. Similarly, you'll have:

Date = Date

oneAM <= f_Time

fivePM >= f_Time

for the second case and

Date = Date

oneAM <= f_Time

midnight >= f_Time

for the third. Then, you can use calculation fields in your parent table equal to:

Sum ( betweenOneAndNoon::f_Number )

Sum ( betweenOneAndFive::f_Number )

Sum ( betweenOneAndMidnight::f_Number )

(Notice that, in the third case, you could strictly use the date, assuming you have no records between 00:01 and 00:59. But I wrote it this way for consistency.)

Make sense?

Mike

• ###### 4. Re: Add fields based on criteria of records

Mike,

This is making sense just fine but putting it in the database gets to be a challenge for me. I am not what you would consider a "Power Developer" so I do appologize if I don't seem to be getting it.

What I have done is to create a field:

f_ONEAM = GetAsTime ( "01:00" ) = (table::f_Time)

f_NOON = GetAsTime ( "12:00" ) = (table::f_Time)

This comes up as "0" in the calculation, even in the records that have numbers in the f_Number or f_Time field. Did I do this correctly or am I supposed to come up with a number?

The relationship I created looks like this:

f_Date = f_Date

and     ONEAM <= f_Time

and     NOON >=f_Time

When I set my calculation field to the relationship table for the NOON calculation I come up with nothing.

SumNoon = Sum (table::f_Number)

I think this may be where my problem is.

• ###### 5. Re: Add fields based on criteria of records

Your calculations are off. They should look like this:

GetAsTime ( "01:00" )

GetAsTime ( "12:00" )

That's it. They're constants. (You can even set them as global calculations if you like; won't affect anything.)

Mike

• ###### 6. Re: Add fields based on criteria of records

Ok,

I have the OneAM, Noon, FivePM and MIDNIGHT fields set to "GetAsTime ( "01:00" )", etc.

The relationships that I have created look like:

f_Date = f_Date

and     ONEAM <= f_Time

and     NOON >=f_Time

f_Date = f_Date

and     ONEAM <= f_Time

and     FIVEPM >= f_Time

f_Date = f_Date

and     ONEAM <= f_Time

and     MIDNIGHT >= f-Time

The relationships have on the Table 2 side, "Allow creation of records in this table via the relationship" as checked.

Then I created calculation fields for SumNoon = "Unstored, from table 2, =Sum (table 2::f_Number) with "Do not evaluate if all referenced fields are empty" is unchecked. I created one for FIVEPM and MIDNIGHT as well.

When I put the "SumNoon" calculated field into a Portal on the layout with the other fields I get a "?".

Any ideas as to what I am doing wrong?

Thanks

• ###### 7. Re: Add fields based on criteria of records

Oh, you're so close!

Problem is here

SumNoon = "Unstored, from table 2, =Sum (table 2::f_Number)

SumNoon = "Unstored, from table 1, =Sum (table 2::f_Number)

Why? Because the "from table 2" part is telling FileMaker, "Evaluate this calculation as if you were sitting on the other table over there." So, since the calculation is evaluating inside table 2, it's completely ignoring the relationship; it's just evaluating inside table 2. Evaluating it from the parent table will fix the problem.

Mike

• ###### 8. Re: Add fields based on criteria of records

That's it!

Thank you very much for your help. I think I am getting this a little better now. I am going to spend some time really studying the Self-Join relationship because I really think it will help my database designs.

Thanks again.

• ###### 9. Re: Add fields based on criteria of records

I think I jumped too soon.

Now this only displays the data from the 1:00 hour. Not from the other hours.

Not sure how I made that happen.

• ###### 10. Re: Add fields based on criteria of records

I might also need to tell you that all 24 hours in a day are on one layout in "List" mode using a Find for the specific day.

• ###### 11. Re: Add fields based on criteria of records

Try the attached. A picture is worth a thousand words.

• ###### 12. Re: Add fields based on criteria of records

Thanks Mike,

That made all the difference. What I found was that I did not label my Table Occurrences in the Relationship correctly. Once I saw how you did that then it all came into focus.

Thanks again.

Wayne