Struggling with Lookup

Rest assured, you’re struggling with one of the more difficult things to do using Airtable. However, it can be done — and the following post and referenced base will go a long way to showing you how:

Note: The examples in that base deal with calculating a running balance, which, because of circular reference errors, require a somewhat atypical work flow. In your case, you won’t need the copy/paste step necessary to bypass Airtable’s self-reference checking.

Son of note: The next few sentences will mean little until you’ve read the earlier-referenced post and, quite possibly, the posts mentioned in the sentences themselves.

On the other hand, you will need to calculate both a bought and a sold value for each week. Your idea of using the week of a transaction is dead-on, although I’d use DATETIME_FORMAT() with the 'ww' format specifier, as this always returns a two-digit indicator:

DATETIME_FORMAT({SoldDate},'YYYY')&DATETIME_FORMAT({SoldDate},'ww')

assuming {SoldDate} was January 1 through January 7 of this year, would return 201801. Use that as the key for your key:value pairs. You may also want to look at the information in these threads and the bases they reference:

(With the last example, please be sure to scroll to the end of the replies to find a link to a revised version of the base I originally provided.)

I realize, looking over the recommended posts, this may appear daunting, but I suspect that has more to do with my unfortunate wordiness than with the difficulty of the concepts and methods described. As always, if you have questions or run into problems, don’t hesitate to ask.

@W_Vann_Hall’s solution may indeed be what you need (and it’s ingenious).

Depending on how you have your records (lot, item, etc) set up, there may be a slightly simpler, but also less powerful (in terms of being able to do anything else with it) way to see weekly profits.

I’m going to show you something I’ve done in one of my bases, but you’ll have to modify it for your situation - I don’t have a good grasp on your setup, so I don’t want to venture so far as so suggest exactly how you should do it.

In whatever table it is that you want to see “weekly” profits, first, make sure you have field that formulates profit per record (whatever the record represents, be it a “lot” or an “item”), and a date field that allows you to record when that profit was realized (I’ll refer to it as {Profit Date} below).

Next, make 2-3 new fields, depending on how much grouping you want to do:

• A formula field to isolate the Profit Year:

DATETIME_FORMAT({Profit Date},'YYYY')

• A formula field to isolate the Profit Month (optional):

DATETIME_FORMAT({Profit Date}, 'M') & "-" & DATETIME_FORMAT({Profit Date},'MMM')

• A formula field to isolate the Profit Week:

DATETIME_FORMAT({Profit Date}, `w`)

Now hide all 3 of those fields in your view, but use them to Group your records on - first by Profit Year, then by Profit Month (optional), then by Profit Week. This will allow you to utilize Airtable’s built in column calculators to see Profit totals for each Group - Year, Month, and Week. In my example screenshot below, it is showing me different values (Avg Diameter, Avg % Design Strength, and Sum (total) # of Samples) aggregated by different periods of time:

You can change the formula output shown for a column by clicking on Airtable’s calculation bar:



I’d play around with that idea and see if you can make it work for you - it may prove to be pretty flexible, and Airtable is doing the “LOOKUP” for you.

EDIT: Another nice feature here is that these groups are collapsible, to allow the hiding of a good portion of data that you don’t need to see at a given moment:

Not to turn this thread into a gooey love-fest, but @Jeremy_Oglesby’s excellent suggestion brings up a good point: Most of my designs are dumfoundingly literal implementations of what a poster has requested, rather than what may often be a more wholistic approach that makes full use of Airtable views, grouping, and filters.

I suspect the difference lies in our (Jeremy’s and my) experience with Airtable away from this forum: Mine has been focused primarily on bases used by one or two people, often in service of a very specific goal, while Jeremy (I’m led to believe; stop me if I’m wrong) has more often been involved with folding newly minted Airtable solutions into existing workflows, replacing or augmenting legacy systems, and often touching a disparate user base. As a result, I often find his suggestions (and those of @Julian_Kirkness, as well, the non-Zap-powered ones, at least) have a more organic feel to them — more ‘here’s what I’ve learned’ rather than ‘here’s what I’ve figured out.’

Which is the better approach? The one that best fits your and your users’ needs and expectations.

Spot on, @W_Vann_Hall.

Sometimes there’s a simple solution that was hiding in plain sight the whole time. Sometimes there’s just no way to accomplish a particular thing in Airtable without a signature, @W_Vann_Hall, 425 nested IF()'s statement with MOD()ed factorials generated from DATETIME_PARSE() CONCATENATE()ers! (no, that doesn’t make any sense, but hey - it’s not far off… )

LOL. I think you have some fans! I think I left out some important info–I’m having to set this up, because I don’t have a simple way to calculate profit per item. I buy a lot (say $100) and it has 20 items in it (sometimes 2, sometimes 200). The items all sell individually for different amounts at different times, so I don’t have a way to accurately reflect what portion of the initial COG should be attached to it. That’s why I’m going with the weekly version, and having this struggle. I’ll see what I can piece together!

Can you tell me if I missed it? The biggest challenge I’m having is how to perform a LOOKUP in an unlinked column.

That’s easy: If I understand what you mean by

the answer is, you can’t.

If you use my method, you’ll have a [Transactions] (or something similar) table where each record represents a purchase or… hmm. Actually, this is going to be a little more complicated than I thought.

Let me kick this around a bit over the weekend, and I’ll see what I can put together.

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]:

1. 
   

   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]

   
   


2. 
   

   Only for item a link is established to the {Lot} from where the item originated.

   
   


3. 
   

   The lot or item price, description, and any other pertinent information (vendor/customer, marketplace, and so on) are entered.

   
   


4. 
   

   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.

1. 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.


2. 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.


3. 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.


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.

Thank you very much for the time and attention you’ve given to this challenge!!