Hey,
So this is a bit of a special behavior when using the "create" option on coalesce fields. In normal cases it should create that reference schedule entry. And it would. But in your case it is also a coalesce field, which complicates things because of the order of operation. Here is my interpretation of what is happening.
With the "create" action, the missing referenced entry is created after the mapping has happened, but before the record is entered into the database. This creates a conundrum for the mapping itself as the coalesce value in this case is empty - the schedule does not exist at the time of the mapping. As you've not selected the option "coalesce empty fields" the mapping will ignore any empty values for your coalesce field - treating it as if it is not there.
So the fallback for the mapping happens: checking for your other two coalesce values. And as this is your start & end, it will find too many records to match. In short: Because the referenced schedule does not exist yet your mapping itself is empty. Because it is empty on the time of the mapping, it will be treated as an empty coalesce value.
How can you remediate this?
In your mapping script (the one you have screenshotted) you are "just" checking on the schedule name. If such a schedule does not exist, your mapping fails. So your best option is to change this reference lookup to be just that - a reference lookup.
First, change the mapping value to be just "schedule" instead of "schedule.name". Then add the following code:
answer = (function transformEntry(source) {
// let's query for the schedule
var schedule = new GlideRecord('cmn_schedule');
schedule.addQuery('name', source.u_email); // -> let's look for a schedule with that name
schedule.setLimit(2); // we want to make sure there is only one
schedule.query();
if(schedule.getRowCount() == 2) // whoops, there is more than just one
return; //-> ideally you have some error handling here, but we just want to make sure that we don't have duplicate schedules
if(schedule.next())
return schedule.getUniqueValue(); //-> found exactly one which we can map
// -> now the only option left is none was found -> we need to create one
var newSchedule = new GlideRecord('cmn_schedule');
newSchedule.initialize();
newSchedule.name = source.u_email;
// add more fields depending on what you need to be filled in for the schedule record
var newScheduleID = newSchedule.insert();
if(!newScheduleID)
return; //-> again, error handling, the new schedule could not be created for some reason
// -> only option left is we successfully created the schedule entry
return newScheduleID;
})
Just as a heads-up: this code is not fully tested, so please use at your own risk. Also, please make sure to add proper error handling.
After this your coalesce field is indeed filled with a correct schedule. And the schedule is entered before the actual insert of your row happens.
The alternative would be to check the checkbox "coalesce empty fields". However this assumes that you do not have any entries already where the schedule reference is empty - an assumption i would deem as risky (as otherwise this would be allowed to map to entries where there is no schedule referenced).
Let me know if you need further explaination.
TL;DR: If you map on references, you can run into the issue that no reference is found. The mapping then ignores that reference. So make sure the reference exists before the mapping.
Regards
Fabian
