Overriding related values for specific records

[JD2775]

Hi there,

I have a layout that has data from Table 1, a Name andDate field, with a portal for Value field. The Value field is stored in Table 2 and autopopulates depending on what Name is chosen from Table 1. I need the ability to override the data in the Value field, but NOT change it's original value in Table 2. Is this possible? For example if I enter the Name "House 1" and "2000" pops up in the Value field. I need to the ability to change it to "1500" for example, but the next time I pull up "House 1", have "2000" autopopulate again.

Basically the values hardly ever change with the records, hence the looked up table of values, but they DO change occasionally on the fly.

Thanks

[comment]

I cannot make sense out of this:

The Value field is stored in Table 2 and autopopulates depending on what Name is chosen from Table 1. I need the ability to override the data in the Value field, but NOT change it's original value in Table 2.

I suggest you use real (or at least meaningful) names for your tables and fields. The term "autopopulates" is also not clear. In general, a looked up value can be modified without affecting the original (lookup source) value.

[JD2775]

Sorry, I used those names as an example. Maybe I should just use the actual names, it may make more sense.....

tblMaintenance has fields:

WarehouseID

Date

tblSquareFeet is a lookup table and has fields

WarehouseID

SquareFeet

That table lists all the Warehouses and Sq Feet for each warehouse.

Tables are related by field "Warehouse" obviously

On the layout I have Fields:

WarehouseID

Date

and a portal from field SqareFeet that populates based off Warehouse chosen,

There are other fields as well, but they are not related fields and don't really affect this question at all.

In general, a looked up value can be modified without affecting the original (lookup source) value.

That's exactly what I was wondering, what settings do I need to make field SquareFeet in order to have it operate like that? I have tried a couple of settings and I either get "This field is not modifiable", or I can modify it but it modifies the original value as well.

Thanks

[Cabinetman]

EDIT:

WarehouseID

Date

add - SquareFT which is a lookup

So you can go to the SquareFT AFTER it "autopopulates" and type in whatever you want and commit it to that record without changing it in the original.

[comment]

There is something missing here, either from your description, or - more likely - from your structure.

and a portal from field SqareFeet

A portal cannot be "from field". A portal is to a table. And you have no table here that a portal to would make sense.

If, as I suspect, a maintenance can include several warehouse, then you are indeed missing a third table, say MaintenanceItems - and your relationships should look like this:

Maintenance -< MaintenanceItems >- Warehouses

I suggest you take the demo here:

http://fmforums.com/...hp?post/309136/

and change Invoices to Maintenance, Products to Warehouses and LineItems to MaintenanceItems - and Price to SquareFeet.