It would seem to be the count aspect that is the nub of your issues.
You are using the primary key (PK) in the rel, and so you are only ever going to have a valid REL , when there is a record, and hence only ever a value of 1 (ie. self).
But that is not the real issue.
What you are attempting to do is count material_lot_no by material_name, but by using the material_id rel, there will only ever be one related (self) record
i.e you have category and item data, need to count by cataegory (material) , but are using the item id for the count
Now normally, one would have a table of Materials ( IsoPropyl, Ethanol..) and a second table of actual brand/size/grade actual physical entities. So a winchester of ethanaol GP would be a different record to Ethanol Spectro etc.
What you are missing is cataegorising what are line items by a parent (material) category.
So a parent child structure is really required. The child table , actual physical entitites, would have its own primary key ( inventory_id or whatever), and also a foreign key material_id.
The count in Materials would then use a rel to inventory using material_id.
Or if you prefer MaterialType and Material tables.
As it is, you are trying to put all your winchesters in one table. You might get away with this if you were manually entering the material_id, but then it would not be a PK.
What you could try, if you want to maintain your simple single table structure, is creating a a REL between MaterialName and using that for the count REL. Not what I would at all recommend for a real database ( ie name based rel) as fraught with potential issues, and is just plain poor design. Using the material code would be no better. But this in the first instance will illustrate the count apsect.
The PK for a table should ALWAYS be defined as ; serial, unique, not null, immutable. You have serial as the only definition on material_id
1). vaildation not empty, unique value, uncheck allow user override
2). auto enter tab; serial ( and a alpha prefix helps clarity in numerous instances e.g M0000001) and check probibit modification during data entry
You are on the right track looking to use material_id, but the single table structure is the limitation.
While you could craete a value list for material_name, and have mutiple ethanol records etc, it would only be a half way house. Create a new table for materials, and redefine the current material table material name field as material_id. A popup menu and value list can then be used to enter the id but display the related name.
As for the colour embedding in the calc definitions, making life harder for your self, just define conditional formats on the text annotation flag fields ( eg expired_alarm_text / Expired Alarm)
e.g. If( Get ( CurrentDate ) > Supplies::Expiry_Date ; 1; 0) ; file red, text white
A couple of peripheral observations;
the naming convention you have adopted is good, and the keying name material_id with _id suffix is what I use too. I would drop the uppercase in field names, adds nothing to readability and just requires extra key fingering