Relating to a calculated value

[Transmasco]

In part of the solution I'm building I'm trying to track trires. I have a tire table that each record represents a tire. Related to the tires table, I have a tirecheck table (related by tire ID) which displays as a portal in tires and records every time the tire is checked. I created unsorted calc fields on tires (CurrentLocation, CurrentPosition, CurrentTreadDepthto,etc) that display the last occurance of the portal i.e. =last(tirecheck::field). This to reflect the "current status" of the tire.

I'd like to relate (and self join) to one of these calc fields but FM tells me I can't relate to globals or calcs that use related fields.

How can I go about this?

[underhat]

Hi Transmasco,

This is correct. There is no way to relate to an unstored calculation, and using related data makes it unstored. It's not an easy answer. There are various other methods which might be applicable here, but I'm going to need more information about what you're trying to do and why before I can guess at what might help you.

What's the larger context of the solution? Why are you trying to relate to the current status of the tire? Can you think of a way that you could accomplish what you need without relating to the current status?

[Transmasco]

What's the larger context of the solution? Why are you trying to relate to the current status of the tire? Can you think of a way that you could accomplish what you need without relating to the current status?

Hi underhat,

The larger context of the solution? Oh man, I don't think I have the time to answer all that

This is a small part of the whole solution. I want to track tires for many reasons: 1) keep an inventory of them 2) track where they are (they get stolen or sold. not gonna get into that) 3) keep a record of performance (which brand/tread/re-tread/type costs me less 4) What drivers abuse the tires 5) Manage warranty claims, etc. etc.

Tires get surveyed every couple of weeks so we monitor wear and all the above info. The Tires table reflects each tire in a record (Brand/size/new/retreaded/optimal pressure/date purchased/cost/etc.) The TireCheck table is a portal of the tire table which displays (actually data gets entered here) the info for each time the tire has been surveyed (date checked/tread depth/pressure/truck its on/position on truck/etc). I have calc fields returning the last portal record info as mentioned in my prior post. This for when I print out a list (sorted by truck) I get the latest info available.

I also have a Vehicles table on which I'd like to display this up-to-date information on. So I related Vehicles to Tires using VehicleID=LastLocation. LastLocation is one of the calc fields that returns Last(TireCheck::Location) but this is why the relationship doesn't work. I'd also like to self-join the Tires table to create a layout where the tires actually display in their position (portal record 1= Position01=Front left Tire) and arrange the portals to resemble how they are in the vehicle.

Hope this isn't more confusing

[NFD]

Tires table doesn't appear to contain either VehicleID or LastLocation. So I'm not sure what the basis for the relationship between Tires and Vehicles is?

Both the Tires and Tirecheck tables should probably contain [TireID#] and [VehichleID#] fields. The Tires and Vehicles tables might use auto-entered serial numbers for respective fields. Tirechecks will get both numbers from relationships as described below.

As to the second part of your question, there are several ways to do this, but a simple way is;

In the Vehicles table, create global fields (e.g., gLF, gRF,gLR & gRR) for each wheel location and enter "LeftFront", "RightFront", "LeftRear", "RighRear" (or whatever values you're using in Tirecheck::PositionOnTruck) in each respectively (these no longer need to appear on a layout). Then create separate relationships between Vehicles and Tirecheck) for each wheel location in which;

FMP7[vLeftFront] ~ Vehicles::VehicleID# = Tirecheck::VehicleID# AND Vehicles::gLF = Tirecheck::TirePosition

FMP7[vRightFront] ~ Vehicles::VehicleID# = Tirecheck::VehicleID# AND Vehicles::gLR = Tirecheck::TirePosition

etc.

Define each relationship to allow creation of related records in Tirecheck table, and to sort data in descending order by Tirecheck date (auto-enter CurrentDate). Then place portals for each relationship on a layout in Vehicles as appropriate.

Each portal will then display the current TireID# at each wheel location in the topmost row. To create new related Tirecheck records, you can;

1) copy the TireID# from the top row of a portal and paste it into the bottom row (Wheel rotations can be handled by pasting TireID# in bottom row of other portals, OR by changing the value in wheel location). This simultaneously enters the VehicleID#, TireID# and wheel location for the new Tirecheck record.

2) scripted creation of new related records for each wheel location. This can either automatically enter TireID# from previous Tirecheck at each location, or leave for user to manually enter.

[underhat]

Transmaco, NFDs solution is an excellent one.

And I want to underscore that it's always a good idea to have an auto-entered serial number field as an ID number, so that each vehicle is assigned a unique number, each tire is assigned a number, etc.

You sounded like you;re using Location as the primary key (that is, the ID) in the Vehicles table to identify vehicles? It's generally not a good idea to use "meaningful" data (i.e. data that has meaning for the user) as the primary key -- you tend to run into issues later where people want to change it, but that's not so easy because of all the related records. So instead, you create some auto-enter serial number fields (which you don't even have to show to the user) and you use those in the relationship. Each Tire record knows which Vehicle ID# it belongs to; each Tirecheck record knows which Tire ID# it belongs to, and then the Tirecheck record can also have a Vehicle ID# which gets entered by lookup.

Let me know if you're not sure how to set this up. Are you working in FileMaker 6 or 7?

[Transmasco]

Yes, I do make auto enter ID's to every table. I am using Location as key to Vehicles as locations include vehicle ID's (also includes Trash, warehouse, etc). Positions are described as numbers (Front Left =01, Front Right =02, etc)

I'm thinking of simply changing the calc fields to regular text and number fields and make an "Update" Button/Script using Setfield =Last( TireCheck::fields).

Gonna try out some stuff. Thanx guys