Replacing SOQL By SOSL- facing issue

Question

So, I've parent object and approx 17-18 child object(most connected via Look ups relationship). Data goes into child object based on some conditions. Not all child objects are required to be filled at one point of time. There is a UI on externally facing community which is used by community users to fill data (which in background fills all child object records and Parent.)

If child object is filled, then there is a field(Checkbox, boolean) which decides, if this child record is valid or not. This is auto calculated by system via trigger. This field exists on all child objects.

Parent record, can be said valid, if this checkbox(boolean) is true for all child objects.

Now problem is - , when community user fills data and click submit, at that point, system runs SOQLS for each of child object to find, if it is valid or not. For this around 17-18 SOQLs runs at that time. Besides this, lots of business rules and we frequently encounter 101 SOQL issue.

We're thinking to remove these 17-18 SOQLs by a single SOSL, which can query all child objects at once. But wondering, is this possible ? For e.g. what should be searchString for SOSL? Also, I can't search checkbox(i.e. field that decide, child record is valid or not). Are there any alternative efficient solution available ?

Unfortunately, not able to think any other good solution on this and need community help.

Answer 1

Not sure SOSL will really fit at your design, but you can take this approach which might reduce some SOQL queries.

The way you have created 17-18 child objects, you will create 17-18 boolean fields isValidObject1, isValidObject2 etc for each child objects at Parent level. By default mark those boolean valus as true since, Parent may not have child records in some cases.

Write trigger on individual child objects which will execute during insert/update to check if all the related children of that Parent record is valid and if so, then update isValidObject1 to true otherwise false.

To know Parent is valid or not, create a formula field and club all 17-18 boolean values to return final result.

This way, you can eliminate some of the SOQL queries, but you can think other most robust way.