[SalesForce] Access related object via lookup field in email template

You need to assign the ID (not the Name field) of the ‘Sales Revenue’ Account__c to the productsale.Account__c.

The error that you are getting is because your query queries the Product_Sale__c object but attempts to assign to the Account__c. The object that you query must be assigned to that type of object.

Your query should change to the following:

Account__c defaultSaleAcc = [
    Select Id
    From Account__c
    Where Name = ‘Sales Revenue’
];

Your loop body should change to assign the ID

productsale.Sale_Account__c = defaultSaleAcc.Id;

Lookup fields store the ID of the related record, not the name.

Like @JimRae commented, the trigger fires after the user hits the Save button. If you want the Lookup field pre-populated before the user hits the Save button the only option is a custom Visualforce page or implementing the LKID url hack.

Alternatively, you could check to see if the value has been not been set (i.e., == null) in the trigger and then set it to the default if it hasn't been set. Depending on your business rules you may only want to do that on the initial insert and you could add some help text about the defaulting behavior. Note that this alternative approach doesn't display any value until after the Save.

If your VF page has standardStylesheets="false" then the button won't show up next to the input box. I copied this from salesforce common.css to use in my pages that don't use the standard style sheets.

/* Salesforce css for lookup button */

.lookupInput
{
    display: inline;
    vertical-align: middle;
    white-space: nowrap;
}
.lookupInput img
{
    background-repeat: no-repeat;
    margin-right: .25em;
    vertical-align: middle;
}
.lookupInput .disabled
{
    background-color: #ccc;
}
.lookupInput .emptyDependentLookup
{
    font-style: italic;
}
.lookupInput input[readonly]
{
    background-color: #e6e6e6;
    border: 2px solid #e6e6e6;
    color: #333;
    cursor: default;
}
.lookupInput a.readOnly
{
    float: right;
}
.lookupInput span.readOnly
{
    display: block;
    white-space: normal;
}
.lookupInput span.totalSummary
{
    font-weight: bold;
}
.inlineEditRequiredDiv .lookupInput img,.inlineEditDiv .lookupInput img
{
    vertical-align: middle;
}
.quickCreateModule .lookupInput input {
    max-width: 155px
}
.lookupIcon {
    background-image: url(/img/func_icons/util/lookup20.gif);
    background-position: 0 0;
    width: 20px;
    height: 20px;
    background-position: top left
}
.lookupIconOn {
    background-image: url(/img/func_icons/util/lookup20.gif);
    background-position: 0 0;
    width: 20px;
    height: 20px;
    background-position: top right
}