How to preserve location based medical history during bird moves

Ask for Help
1

The Goal: I am building a poultry management app to track 1,000 birds. I need a unified “Medical History” for each bird that stays intact even when they move between locations.

My Current Setup: I have a Health_Events table that acts as a single source of truth. It uses a TargetID to link events to three different levels:

  1. Batch Level: (e.g., “Batch 101” got Newcastle vaccine).

  2. Location Level: (e.g., “Area 1” got vitamins in the water).

  3. Individual Level: (e.g., “Bird #55” treated for a wound).

In my Inventory Table (Individual Birds), I use a Query Column to pull all matching events from the Health_Events table where:

  • TargetID = This Bird’s BatchID OR

  • TargetID = This Bird’s BirdID OR

  • TargetID = This Bird’s current Location.

The Problem: The “Location” history is ephemeral. When I move a batch from Area 1 to Area 2, the Query filter in the Inventory table updates to “Area 2.” Consequently, all the historical medical records linked to “Area 1” disappear from the bird’s timeline because the Location match is no longer true.

The Strategy I’m Attempting: I want to use a “Move Button” that performs a mass update (moving 1,000 birds at once) while simultaneously “stamping” the old location’s history into a permanent record before the birds leave.

Where I’m Stuck: I am trying to use a Helper Table (a single-row “Scratchpad”) to capture the Move details (Source Area, Destination Area, and Move Method). However, I’m having trouble:

  1. Passing the “Source” selection from the Helper Table into an Add Row action for the Health_Events table.

  2. Ensuring the “Location-specific” meds from the old area are permanently “attached” to the birds/batch so they don’t vanish after the Location column in the Inventory table is updated to the new area.

Question for the community: How can I best “snapshot” the current location-based history and re-attach it to a Batch or Bird record during a mass move action so the timeline remains complete?

2

You could use an additional column for the current snapshot. And use this column in your queries. Did I understand the need correctly?

3

How many times do they change locations?

If only once, create a separate column to store the original location value before overwriting their new location.

If more than once, you’d really want some sort of “bird locations” table (big table) that records the bird id, location, timestamp, etc. Whenever you create a new bird id, also create the record in the bird locations table. When birds move, just add new rows to this table. When querying, sort the query by timestamp in reverse order to get the latest location for each bird.

1 Like
4

Is it feasible for you to fetch all related IDs in a medical event (whether it be batch or location) and write it as a comma-delimited string to a basic column?