I tried making a field for "Cell B2" called "Full Camera Set" and added this calculation.
If (LINES::items_out ≥ 1 and product_category = " A Full Camera Set";1;0 )
So basically if Joe Smith has A full Camera Set ( Product A ) which would check from the line items having a value of 1 or more than place "1" in the cell or "0" otherwise.
If you have the typical table/relationship structure used for invoicing:
Then a List View layout (instead of table view) could be set up on a layout based on invoices with a series of filtered one row portals showing data from line items in columns. The Filters would select for specific product ID's.
Instead of filtering by specific product ID's could I filter for certain product categories " text values" instead of product Id's ?
So product A searches / filters for Cameras.... Product B searches/ filters for Computers etc.
Do you mean that each "Cell" would be it's own individual portal ?
Each cell would be a portal row in a one row portal. And the portal filter can filter by a category instead of a prodect ID, but then you'll have an issue where a given invoice might have more than one item listed in that category. (That could also happen with product ID's). A summary field defined in the portal's table can be used to display an aggregate value such as the total of all such items for the given invoice.
instead of a filter would it be possible to have a field in each of those portals that has a specific defined calculation so that
the field "Product A" needs to search the Inventory::product_category field to see if the text entered = " A Camera "
If Joe Smith has "A Camera" out as indicated by the search in product category and the search to see if the Lines::items_out > or = to 1
then place the amount of Lines:items_out in the field "Product A"
Yes, but that would require defining a different relationship to a different table occurrence for each column of such data on your layout. That adds a lot of complexity to your design.
A "count of" summary field that returns the number of such items in the category would be much simpler to set up.