Well, I almost have what you were looking for.
Here is a bare-bones base demonstrating per-lot and per-week P/L calculations. I have to confess it still requires a degree of manual intervention to generate per-week calculations – but, then, any solution I could come up with would require some level of manual manipulation. The way I ultimately decided to approach this differs from how I first anticipated handling it, but it requires no more and no less manual work than my initial plan.
There are four tables in the base. Obviously, you’ll want to flesh them out, adding vendor and customer names and addresses, for instance, but the core of your P/L calculations are demonstrated here.
Your two main tables for data entry are [Lot] and [Item]. I’ve slightly modified your naming formula for [Lot]; each lot is now named
L(YYYY)-(WWWW)-(#####)
where (YYYY) is the year, (WWWW) is a four-digit representation of the week since Unix Epoch (more on this later), and (#####) is a five-digit, zero-padded representation of the record’s autonumber. I went with (WWWW) rather than the more straightforward week of the year to avoid potential data corruption after the year rolled over. (Yes, I could have used 'YYYY-WW' as my week-identifying key, but there are some data manipulations easier to perform based upon a unique identifier for the week.) The week number is derived based upon Unix time, so this base will stop working on January 16, 2038.[1]
The naming convention for [Item] is the same, except the name begins with 'I' rather than 'L'.
Essentially the same process is used to enter data into [Lot] and [Item]:
-
The transaction {Date} is entered; however, rather than date value being entered directly, a link is established to the appropriate record in the [Date] table; if the appropriate record doesn’t exist, a new record is created.[3]
-
Only for item a link is established to the {Lot} from where the item originated.
-
The lot or item price, description, and any other pertinent information (vendor/customer, marketplace, and so on) are entered.
-
Once the date is entered, both the {Name} and the {Week} are generated.
There are two week-related fields in [Lot] and [Item], {Week} and {Link to Week}. This is where the manual data manipulation takes place.[4] The value of {Week} must be copied into {Link to Week}. This can be done in either of two ways: By copying (Ctrl-C) from {Week} and pasting (Ctrl-V) into {Link to Week}, or by selecting the fill handle in the lower right-hand corner of {Week} and dragging it into {Link to Week}.
Note: Multiple cells can be copied at once using either of these techniques.
This last step creates a link to an existing or newly created {Week] record, allowing per-week P/L to be calculated.
Note that, if you wish, [Item] records could be entered in advance of their sale — for instance, while breaking down the lot for resale. This would prevent you from having to look up the lot number each time an item sells. You could also create an [Item] view filtered by sale price — that is, showing only those items where {Price} is blank — to show you inventory on hand.
The other two tables, '[Date] and [Week], exist mainly to provide a viewpoint into the data. Within the [Date] table, the view <Grid - grouped by week> provides a day-by-day look into each week’s transactions. In [Week], the <Grid - per-week P/L> view offers, unsurprisingly, the per-week P/L which you requested. In addition, within the [Lot] table, the <Grid - per-lot P/L> provides just that: a per-lot P/L calculation.
And the rest is footnotes.
- For any Unix experts reaching for their keyboard to correct me by saying it’s actually January 19: Yes, you’re right — except I fiddle with the date to take into account Unix time having begun on a Thursday. The week returned is that of the logged date plus three days,[2] to provide for a Sunday through Saturday week.
- Actually four days, to get around Airtable’s sometimes non-intuitive handling of GMT versus local time. This value may need to be changed, depending upon your relationship to GMT and which day your locale sees as the first day of the week.
- There is a chance this selecting of a date from a long list of date record IDs could become annoying, given Airtable’s current inability to filter linked records. Should that be the case, you might want to create a
{Date} and a {Link to Date} field — or even a {Today} and a {Link to Date} field — and populate the second by copying the first, as described in step 4.
- Actually, the copy/paste of
{Week} doesn’t have to be performed for each lot or item; should you prefer, the values for an entire day or week could be transferred at once. Choice whichever work flow feels most natural.
