Setting fields in another table

[Hutch69]

Just got up from the sword I fell on. New at FM.

Writing a DB to barcode scan issued firearms and equipment for a duty shift - both in and out.

Have a History table that records various things including the person issued to, time in and out and the equipment ID.

Also have an equipment table that records items, serial numbers etc.

The equipment table has a flag to record whether the item is in or out. My sign out script sets the flag to '1' by simply

set field [Equipment::is_out; 1]

I can't seem to manage to reset the flag to 0 upon equipment return.

I need the flag to be in the equipment table as I use it to provide a count of items in and out to enable a shift handover.

I guess the question is - when I change layouts to set a field - how can I navigate to the corresponding record to change the flag?

I'm sure this is a simple to all you FM guru's out there!

[bcooney]

The History table has an equipmentID, right? Create a relationship from History to Equip by EquipID. Then your "return" script can set the equip flag thru the relationship, without switching layouts.

Set Field (history_Equip::is_out; "") //return

or

use GTRR History to Equip by EquipID and set the Equip field.

[comment]

I would make the status field a calculation - eliminating the need to set the field, as well as a potential discrepancy.

[Hutch69]

bcooney - Wow - that worked. I think I had actually tried that previously but hadn't ticked the allow creation button. Many thanks.

With reference to the comment of using a calculation for the status - can you give me an example? - It is current a number field with a value range of 0 or 1

All the time spent trying to nut this out - wish I'd found this Forum earlier.

[comment]

using a calculation for the status - can you give me an example?

Well, you could use =

Count ( History::Out ) > Count ( History::In )

for example. If this is for display only, you don't even need that - see attached.

LoanOut.zip

[bcooney]

Allow Create does not need to be checked. However, comment's suggestion to calc the status is the ideal solution.

[Hutch69]

Wow - how simple. Will definitely make things easier, Cheers