I am trying to upsert a large number of records into a custom object "Invoices". This Invoices object is a child of Accounts. Thus, every Invoice record I upsert must be associated with a master Account during the import process. If I try to import an Invoice that has no valid Account master, that record should fail.
I am using the Data Wizard because Data Loader has been having problems on my Mac and dataloader.io will cost a lot of money to import this number of records (about 20k records weekly).
I have an external ID "FN ID" on Account records. I have that same ID as one of the fields on each Invoice record. So it seems like I should be able to tell Salesforce, "See this FN ID field in every record in my CSV file? Find the Account with that same matching value and use that to create the relationship."
BUT no matter what I try, all my records fail. I selected the external ID when Salesforce asked me, "Which Account field in your file specifies the Master/Detail relationship?" And then, in the mapping stage, I tried mapping the external ID in the CSV to the Account__c field.
I get this message: "INVALID_FIELD_FOR_INSERT_UPDATE:Invalid target field for field update.:Account__c --"
The weird thing is this: when this failed, I exported all my Salesforce.com Account IDs and used VLOOKUP in Excel to match them to each Invoice record in my CSV. Then I tried to upload the CSV again. This time, I selected "Salesforce.com ID" when asked "Which Account field in your file specifies the Master/Detail relationship?" and in the mapping stage, I mapped the Salesforce ID I had exported and matched via VLOOKUP to the Account lookup field. I still get the same error on all records.
So I KNOW that these fields match. I've done the matching manually to make sure. This makes me think either this doesn't work technically for some reason, or I am consistently using it incorrectly. I have looked in Trailblazer and across the internet and haven't found any answers that make sense or work.