Not sure if there’s an elegant solution but perhaps someone figured it out and could help:
We have POs for our services. 90% of the POs include only one service item and it’s a unique item for that PO. The other 10% have 2 or 3 items, and again each item is unique to that PO.
I’m looking for a way to include several items for those POs’ records, that wouldn’t be an “items” table.
If the items weren’t unique to a PO, it would have made sense to make a “items” table and link items to the PO. But in our situation it would mean to basically duplicate the POs table, and just add a few more items.
Each item includes only description and price.
Is there a better way of doing it? maybe a specific field that can include it?
Thanks
Sure, a text field can list items (meta-data) about the parent. This article kind’a suggests this approach.
The challenge is unpacking such list. You need a way to parse it when needed.
Since 100% of your POs will have a maximum of three items, and each item will have only a description and price, you could just have six fields in your PO record:
- {item 1 description}
- {item 1 price}
- {item 2 description}
- {item 2 price}
- {item 3 description}
- {item 3 price}
I would only do this if you are very, very, very sure that
- all of your POs will have a max of three items
- all items are indeed unique
- there are no other fields necessary for each item (except maybe a quantity)
- you do not need to see a listing of all items or do any analytics on items
- these requirements will not change
Such a system isn’t elegant, and has its own challenges and limitations. However, if you are under record limit constraints, it might work for you.
2 Likes