Relationship on Calc Field

[stefanshotton]

I have switched database to a separated model. I just cant get one relationship to work???

File 1

Field (1record) vConstant auto entered as number 1

File2

Calc on a fieled resulting in 1 or 0

Added file reference

Set up relation between the above two fields

Layout set up under File1

Portal set up to show related fields in file2

But it doesn't show anything, have checked calc in File2 and it shows the ones and zero's for each record correctly.

Ideas?

[Genx]

you cant relate using an unstored calc in the child TO.. if you can change it to a number / text field that auto enters and replaces existing values.. its a pain in the **** but hey...

genx

[LaRetta]

There is no need to make it standard field as long as it can be indexed. What do the 1 and 0 represent? Unless that calc refers to a global field, a Get() function such as Get(CurrentDate) etc, an aggregate function or another related table, you can probably store it. Give it a try... FM will tell you (usually) if it can't be indexed. And it should store it automatically when called for a relationship unless you specifically selected 'Do Not Store' in the calculation storage options. Also make sure that you don't have the NONE selected on indexing options.

LaRetta

[stefanshotton]

File1 is the GUI

vConstant is a global field auto entered at 1.

File2 is a to do list.

The field referenced is a calc that returns a 1 if the number of days to the Due date of a task is less than 10 days.

so in theory it will only show the items with 1 in a portal.

[LaRetta]

Here's a way to make it work. In File1, create a calculation (result is DATE) called cTodayPlusTen. Be sure to set the Options to 'Do Not Store Calculation Results.' Enter this formula:

Get(CurrentDate) + 10

Join as follows:

File1::cTodayPlusTen :great: File2::DueDate

This will relate to all tasks with a DueDate past due and up to 10 days ahead of today's date. Is what what you want? Again, you can't use Get(CurrentDate) in an unstored calculation in your Tasks file so you need to reverse your thinking here. The separation model should only be attempted by more advanced Developers. It increases the complexity of your solution and isn't necessary in most cases.

Of course this will also show tasks complete (because tasks older than current date will also display). But you would WANT this in case a task isn't done but is older than the current date. Do you have a field which indicates the task is done? You would want to include that in the join. How is task marked as done? If you explain this part, we can tweak this relationship appropriately for you...

LaRetta :wink2:

[stefanshotton]

Hi La Retta

Yes there is a field for marking the task as completed and would not want that to appear int this relationship.

Ideally want the user to specify via an input field the number of days they wish to look forward in their to do list. This has been the stumbling block with coding that in the child table as contains a global field and makes the script unstored, is it possible to do this in the parent table or via $$variables?

[LaRetta]

If a User wants to specify the number of days to look forward, you can provide a global (number) called gNumbDays in which they enter their choice. It makes no difference where this global number is stored. Then User is presented with their task list and they type in a number. BTW, this can be permanently stored in their Staff table (or a Preferences table) as standard data and pre-entered as the default choice for them. But to keep this simple, I won't go into that process. You would adjust the cTodayPlusTen (maybe rename to cTasksDue) as:

Get(CurrentDate) + gNumbDays

You didn't tell me the criteria for how a task is completed so I'm still working in the dark here. I assume User clicks a button or enters data in a field when a task is complete? What is the field name and data type? I can't help you attach this piece until I know how Users mark a task as complete.

LaRetta

[stefanshotton]

The task is marked as complete when a user clicks a button and a "Y" is entered into the "completed" field

[LaRetta]

Okay. Because those not-yet-completed tasks may be used in other relationships or for value lists and such, I would suggest this:

In your Task table (file2), create a calculation called cNotDone (result is DATE) with:

If ( IsEmpty ( Completed ) ; DueDate )

Then join from your main table directly to this new cNotDone instead of joining to the DueDate. If the task is done, no date will appear in cNotDone and so those completed tasks won't appear (because they won't relate). By using another date field (for unfinished tasks), you will have additional power and flexibility of filtering value lists on Staff with outstanding tasks and other such goodies.

I'm surprised you don't use a DateCompleted field to signify that a task is done (and when). A field called DateCompleted which inserts the current date when clicked would work well. In this way, you would be able to track how long it takes Staff to complete their assigned tasks. If you have such a field, use it instead in the cNotDone formula as:

If ( not DateCompleted ; DueDate )

You might also want to protect from the fact that a User may NOT enter a gNumbDays. To do this, you would go to the cTasksDue in your main table and be sure to uncheck in the calc box 'Do not evaluate if all referenced fields are empty.' In this way, a blank gNumbDays will still produce a list of Tasks, but only those that are OLDER than the current date.

LaRetta :wink2:

Edited February 11, 2006 by Guest

[LaRetta]

I forgot to mention that you need to restrict your relationship so it doesn't relate if cNotDone is blank. By using an 'Eden Date', any blanks will not relate because 0 number (blank field) will be less than the Eden Date.

So create a calculation in Main called gEdenDate (result is DATE). I wonder if it would be more efficient to make it a global calculation ...

This EdenDate can use a calculation of: Date(1;1;1)

Add this to the relationship as: Main::EdenDate < cNotDone

If you wish to control the field - I don't like hardwiring calcs, you can do so. But since the 'hardwire' is the beginning of time (Filemaker's first date is Jan 1, 0001, Monday) then you can use it safely to always grab older records, regardless of their date (but not if field is empty). I like it.

You can also use a date from your Preferences file or provide a global date and let Users specify the range themselves. The idea of an Eden date (particularly global calculation) to protect from empty fields when relating seems like a good perm addition to a solution. I usually also use numbers for dates. They are easier and faster (I think).

I admit to being surprised that a relationship was allowed to be established when the right key (cNotDone) was blank. I assume it's because I'm using a non-equijoin. But still ...

LaRetta