I have a complex search that I need help from those search gurus out there wherever you are...
I have a custom record called 'Licenses'. It contains the following:
- a parent-child list/record field for Customer (links to the customer record)
- a parent-child list/record field for Renews License (links to the same custom record type 'License'). This has a 'Source List' identified as the Customer list/record and 'Source Filter' as the Customer list/record. It also has a filter to only bring back Licenses that are active (not expired or pending).
The idea is that when the Customer list/record is linked to a specific customer record, the Renews License list/record populates with all existing active Licenses for this customer (thus allowing the new License record to 'renew' the active License record).
However, there is the possibility that another License record has already used one of these active Licenses to be renewed, so I want to avoid duplicating. I also do not want the list to contain the current record as that would create a pointer to itself.
The pseudo filtering would be something along the lines of:
Find me all License records that are linked to this Customer but is not the current License record and has not been used by another License record already.
I can not seem to do this with the standard filtering available in the custom field list/record definition. If anyone knows how, please advise.
I also tried doing this using script API searches. This I can do, however I can only do it with a two-stage search (which seems like a lot of overhead).
Find all Licenses that have the specified Customer linked and does not match the current record ID. Once I get these results, I then search each record in the results to see if it is already set in another License record.
Ideally I would prefer to do this in one step but can not seem to figure out how since you need to know the ID of each record to test if it has already been assigned. Wondering if others may know a better way of writing the filtering to get it done in one step.
Thanks.
-Steve