Few items per record

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