Ensure Invoice Item Field Value Exists in Other Table Before Save

[nsoftware-george]

My installation has an additional table called "Products", which has several columns, including "Name" and "PartNumber". I would like to build a way to ensure that an InvoiceItem cannot be added unless the PartNumber exists in the Products table.

What would be the ideal way to build this?

I have found these options:

1. Hooks
2. API Before-Save Script
3. Before-Save Custom Script
4. Conditional Logic

I have tried all with little success. If someone could possibly point me to what the ideal way is to do this, I can probably troubleshoot myself through any problems. I just want to make sure I am using the right tool for the job as to not waste time.

[victor]

Just make this field Required and Read-only in Invoice Item.


And in order for this field from the Product entity to be automatically transferred to the invoice Item, use the following functionality: https://docs.espocrm.com/user-guide/...les-order-item. In the documentation, an example is given for a Sales Order Item, but it works similarly for an Invoice Item.

[yuri]

Not recommended. This may stop working in the future as it's against the framework. Unless we will make an exclusion specifically for this case.

For regular entity types this will be causing a validation error. In the future, it may be the case for items too (should be).

[yuri]

I'd stick to API Before-Save Script.

Define a script for the Invoice entity type. That script will loop through items (using while), check every part number in the loop. If it's not empty but there's no product with this number, then throw an error. Use record\exists to check the product.

[nsoftware-george]

Thank you both for your replies. I went with setting the PartNumber field to both read-only and required, which works to prevent saving the invoice item if the PartNumber does not exist. I think it may be nice to implement the API Before-Save Script to get a better error message in the future. Expect a reply here if that happens with details for any other folks that wish to do the same.

[yuri]

I recommend to not to rely on the combination of both Read-Only and Required enabled. This may stop working in the future for Items, as it's against the framework.