Need help separating records

[Hannah_Satterfi]

Hi! Any advice on how to separate a record? For example, we are roasting batches of Coffee. In my “Roast Input” view, they select all the wholesale accounts that the “batch” will go to. Then in my “Account Receiving” view, how do I separate that batch or “record” so that that each wholesale account that receives part of the batch becomes a separate record? Does this make sense? Can anyone help?

[W_Vann_Hall]

What you want is a three-table base (well, to track batches and allocations, at least):

1. [Batch] : One record per roasting batch.
2. [Account] : One record per wholesale account.
3. [Lot] : One record per account per batch.

It’s probably easiest to take a look at this demo base:

Airtable

☕Coffee demo - Airtable

Explore the "☕Coffee demo" base on Airtable.

	Note: It’s actually a four-table base. The fourth table, [Join], the linked-record fields tying it to [Batch] and [Account], the {Current} checkbox in [Batch], and the <Not Allocated> view in [Account] are all window dressing to simplify the lot-allocation process. If you have 25 or more wholesale accounts, you might want to include them, as the small amount of overhead required should pay for itself in usability. If you have 5 wholesale accounts, it’s probably not worth the bother. [In brief, the additional code removes accounts already allocated a portion of the current batch from the list of potential accounts to which to link a newly created [Lot] record, preventing double-allocation.]
	Similarly, the {Accounts} and {Amounts} fields in [Batch] are not required but could be nice to have…

To create a new batch (ignoring for the moment the [Join] table and accompanyign mechanism, do the following:

1. Create a new [Batch] record. Name it and indicate the total amount of coffee in the batch.
2. For each wholesale account designated to receive a portion of the batch, create a linked [Lot] record. To do this
   1. Click the ‘+’ sign in the {Lots} field.
   2. Select ‘Add new record’ from the popup window.
   3. A new [Lots] record is created and opened as an expanded record.
      Under {Account}, click the ‘+’ sign and select the account to add.
      Under {Amount (kg)} enter the amount of coffee to be allocated from the batch for that account.
   4. Click the ‘x’ in the upper right corner of the expanded record view to close the window.

In the [Batch] record, the amount of coffee currently allocated is summed under {Allocated (kg)} and subtracted from the total, with the amount still available to be sold shown in {Remaining}.

That’s all there is to it. You would print packing labels from the [Lot] table, using lookup fields to reference values (address, contact, etc.) presumably stored in [Account]. Each [Lot] record has a formula-created name consisting of the name of the batch concatenated with the name of the account. (Needless to say, all of these specifics can be tweaked or discarded.)

If you want to include the mechanism allowing for a filtered view of accounts, as in the demo, you need to add three more steps to your process. The secret sauce is included in the [Join] table, which contains a single record. In the base as published, the record is named ‘.’ — that is, it’s named [the period character]. You additional steps are

1. Whenever you add a new account, you need to link it to the ‘.’ record in [Join]. You can do this manually or by following one of these methods.
2. Whenever you create a new batch, that batch also needs to be linked to ‘.’ in [Join].
3. When you are allocating coffee from a given batch, {Current} must be checked in the [Batch] record. Only one [Batch] record may be checked at any given time.

I just tossed this together — the description, especially — on a stomach full of antibiotics, decongestants, and NSAIDs. (Oh, the joy of houseguests bearing ugly summer colds as hostess gifts!) If any of it makes less sense than usual, point me at it, and I’ll try again.