+ Start a Discussion
Cynthia DouglassCynthia Douglass 

FORMULA HELP: Creating formulas for an Amount Due section of my Orders form

I'm looking to recreate, in Salesforce, a form that some of our officers use for processing payments.  The part of the form called "Amount Due" requires several formulas, which I'm not sure how to create.  As I'm new to Salesforce and creating formulas, any help would be appreciated!

The form looks like this, with the items in bold (Previous Balance, Credit,Total Due, and Remaining Balance) representing what I need to figure out formulas for:

Subtotal: (Greg Rohman helped me with this, and is an amount calculated, in US dollars, by multiplying # of documents by price per document)

Delivery Charges: (This is a flat fee and is therefore a picklist for either 0 or $15)

Previous Balance: (= Remaining Balance, if positive, from the last time this company did business with us)

Credit: (= Remaining Balance, if negative, from the last time this company did business with us)

Total Due: (= Subtotal + Delivery Charges + Previous Balance - Credit)

Total Paid: (This is manually enterered by our officers, in dollars)

Remaining Balance: (= Total Due - Total Paid; if positive, this becomes the new Previous Balance for the company on their next invoice; if negative, this becomes the new credit for the company on their next invoice)

Would you have any ideas about how to do this?
Greg RohmanGreg Rohman
Hi Cynthia.

I'm a little confused as to how your object(s) are set up. Can you explain the object structure and/or include screenshots?

-Greg
Cynthia DouglassCynthia Douglass
Hi Greg, thanks for responding again!  I'll try to explain the object structure, and also take a screen shot, though I don't know if my answer is what you are looking for!  Some of SF's terms are still very confusing to me, but I'll do my best to respond.

First, I decided that the Orders tab looked somewhat like what I was trying to create, so I renamed it "Certification" since what I building is for a certification of export documents billing process.

Renaming Orders tab to Certification tab

Next, within the Orders tab, I assigned 3 page layouts that are needed by our certification team: one for the certification company clients, one for the form itself, and one for a receipt that needs to generate automatically once the certification form is filled out.  To make these page layouts work, I've been setting up all the fields required by all three layouts.

Orders/Certification tab page layouts

The layout I'm working on that is referenced above is the Certification Form.  This is how it currently looks, and my end goal is to create something close to this, with the same functionality:

Certification Form

The section referenced in my question is the "Calculated Balance" section, what we will now be calling "Amount Due."

Here's what I've got so far for the fields relevant to all three forms.  I still need to create Previous Balance, Credit (previously called Over Paid Balance), Total Due, and Remaining Balance.

Order page custom fields

Does this answer your question?
Parker EdelmannParker Edelmann
This appears to be beyond the capabilities of formula fields exclusively. You'll need a Process on one or more of the related objects to calculate what Previous Balance and/or Credit should equal. As Mr. Rohman mentioned, I'm not sure of how your data model is set up, and how each of the Objects and records interact with each other. If you could help us by explaining the data model and how one would manually calculate everything, we would be better able to arrive at the formulas and Processes necessary to complete this task.

Thanks,
Parker Edelmann
Cynthia DouglassCynthia Douglass
Hi Parker, does the information I provided today, including screenshots, answer your question?
Parker EdelmannParker Edelmann
Oops. Pardon me, I was a little long in posting my answer and in that time you posted more info.

If you want to do it using formula fields on the Orders object, at least one of the fields will have to be a regular currency field that is updated by a workflow or process because the fields would all reference each other and thus form a loop which is not allowed. You mentioned that it would be based on previous Orders, so you would have to have a system of keeping track of the order of the Orders in order for Process Automation to work in the desired way.

However, would it be more feasible to create these fields on the Account object itself? In my opinion (if you're on EE or higher), it would be easier to create a couple of well planned Processes that would update the Account, rather than having to play with the order of the Orders and all of that. Refer to this Trailhead module if you aren't familiar with Process Automation:  https://developer.salesforce.com/trailhead/en/module/business_process_automation
If you need this information on the Orders Object as well as the Account object, you can create a cross object formula field that simply brings the Account information onto the Orders page layout.
Cynthia DouglassCynthia Douglass
Hi Parker,

I'm not married to doing this the way I thought of, since I'm very new to SF and don't really know what I'm doing!  I'm sure an implementation partner would be very helpful, but we don't have a budget for that, so I'm bumping around in the dark, trying to figure it out!

We're using SF Professional.  I'm not familiar with the idea of setting of processes on an Account (which I have renamed to Company), and have never heard of process automation within SF, so I'll follow the Trailhead as you suggest.  This information doesn't have to be on the Orders Object as well as the Account object, it's just what I found that I thought might make sense.  I'll looking into your suggestion of setting up a process on an Account/Company.

Thanks for your guidance, and I'll let you know how my learning goes on this topic!

Best,
Cynthia
Parker EdelmannParker Edelmann
Seeing as how you're on Professional Edition, I had best make sure that you know the limits of Process Automation in PE.
  1. Professional Edition can purchase Workflows at an additional cost. It is an add on package and is not included in base level PE functionality.
  2. Professional Edition in no circumstances can use Visual Workflow
  3. Professional Edition does get Process Builder, however, you can only have 5 processes in the entire organization.
I'm not sure if PE has Approval Processes or not, but that is irrelevant to the current scenario being discussed.

It's your decision if you choose to use one of the 5 process that you are allotted, I recommend using only one per object and only for objects that require lots of automation. With the Summer '16 release, Process Builder was enhanced in such a way that it can do many things in just one process.
Cynthia DouglassCynthia Douglass
Thanks for these additional comments, Parker.  Since I know nothing about process automation, workflows, Visual Workflow, Process Builder, or Approval Processes yet, it will take me some time to understand what you're talking about!

The more I learn, the more I realize how little I know, and how much there is to learn before I can get things up and running ...
Cynthia DouglassCynthia Douglass
So ... I've been learning a lot!  I've got most of my Certification Invoice (custom object in a custom app called Certification) for processing payments working perfectly, thanks to the help of the Community and Premier Support.  Here's what it currently looks like:

Certification Invoice

The Amount Due consists of:

Subtotal - consisting of a rolling summary of all line items;

Delivery charges - a picklist value;

Previous Balance/Credit - formula that looks for the company's most recent previous invoice and if the Remaining Balance is not zero, brings in the Remaining Balance amount into the Previous Balance/Credit field;

Total Amount Due - formula that adds the Subtotal, Delivery Charges, and previous Balance/Credit;

Total Amount Paid - manually entered by the user;

Remaining Balance - formulta that subtracts the Total Amount Paid from the Total Amount Due.

Here's the challenge:

The Previous Balance/Credit is currently referencing the Remaining Balance of a Previous Invoice, but not specific to the company on this current invoice.  The user would need to scroll through lots of previous invoices to find one for the current company, and then plug in the invoice number.  That's not a workable solution, because the users won't have time to do that while the client is waiting for them to certify their documents.

So, the Previous Balance/Credit needs to auto-populate in one of three ways:

1) If there is an Exporter company listed on the current invoice but no Third Party company (such as a courier service), the Previous Balance/Credit would come from the Exporter's most recent invoice Remaining Balance.

2) If there is a Third Party company listed on the current invoice but no Exporter, the Previous Balance/Credit would come from the Third Party company's most recent invoice Remaining Balance.

3) If there is an Exporter company AND a Third Party company listed on the current invoice, the Previous Balance/Credit would come from the Exporter's most recent invoice Remaining Balance.

I have set up a Certification Date on each invoice ({YYYY}-{MM}-{DD}-{0}), which has both a date and a unique autonumber (at least in theory), so this, combined with the name of the Exporter or Third Party company, should be able to identify which invoice is the most current previous invoice to use for pulling out a Remaining Balance.

Would anyone know how to write an APEX code to make this happen?

Many thanks,

Cynthia
 
Parker EdelmannParker Edelmann
Hello Cynthia,

First of all, seeing as how you're on Professional Edition, I'm fairly certain that unless if you pay extra to have the API enabled, you cannot use Apex classes, triggers, etc. or anything that uses code (exceptions may, and probably do, exist).

However, would it be feasible to create a Lookup Relationship to Invoice and add a Lookup filter that narrows down the search? Once this is created, we can then reference any field from that Invoice. You may need to use Process Builder to keep this up to date though.

Regards,
Parker
Cynthia DouglassCynthia Douglass
Hi Parker, 

Thanks for this!  Sorry, I didn't see it earlier in the day.  Good to know that APEX won't work in Professional Edition.  I'll look into the Lookup Relationship to Invoice and lookup filter on Monday and see if I can figure out how to do that.

Many thanks,
Cynthia
Parker EdelmannParker Edelmann
Hi Cynthia, I'm glad to help! There's a treasure trove of knowledge and articles on this Help & Training Search page about Lookup Filters that might interest you: https://help.salesforce.com/apex/HTFederatedSearchResults#q=Lookup%20filter&t=TopResultTab&sort=relevancy&f:@sflanguage=[en_US] Creating a Lookup Relationship should be fairly simple. See this Trailhead Unit for a how-to: https://trailhead.salesforce.com/data_modeling/object_relationships This looks to be an in depth unit, and the module may be profitable for you to go through if you haven't already. Let me know what you find and if a Lookup with filter will work or if another solution should be used, come Monday of course. Thanks, Parker
Cynthia DouglassCynthia Douglass
Okay, I've gone through the resources on lookup relationships and lookup filters (which took much longer than I realized), and here are my goals, using the Professional Edition of SF:

1) Create, on the custom object Certification Invoice page layout, a Related List section called Certification History, that lists all previous invoices for the custom field Exporter Company, if that field is filled in, or for the custom field Third Party Company, if the Exporter Company field is not filled in (either Third Party Company or Exporter Company will be filled in for every invoice; sometimes both will be filled in, but the Exporter Company information would be the priority for looking at Certification History).

2) Pull out the $ amount of the custom field Remaining Value (formula field) from the most recent invoice for the company listed in 1)

3) Insert the value of 2) into my current invoice, into the custom field Previous Balance.

Here's what I've done so far, and there are 3 questions at the end of this rather lengthy description for anyone willing to take a look at this (I am learning that you developers like details!):

I have a custom app called Certification.

I have the following custom objects in the Certification app called:

Certification Document - this brings in line items for the Certification Invoice object, showing # of documents, document destination, and subtotal for each set of documents going to the same destination.
- Certification Invoice - This is the main input form for our certification officers, and records all details necessary to process the certifying of commercial or legal documents, including payment details.
- Certification Receipt - This is as yet undefined, but I would like this to reflect many of the fields in the Certification Invoice, and it would be automatically filled in from the Certification Invoice when a custom button called Certify is clicked.
- Certification Type - This defines two types of documents for certification, either Commercial Documents or Legal Documents, and their price.

Here is how the Certification Invoice currently looks:
Certification Invoice
 
Within the object Certification Invoice I have two choices for companies/accounts: Exporter Company, or Third Party Company.  Both have lookup relationships to Accounts.  I created formula fields ("Company (Exporter)" and "Company (3rd party)" so that the lookup value of Exporter Company the Third Party Company would be a text result rather than a lookup value.

I then went into the custom Previous Invoice field, which has a lookup relationship with Certification Invoice.  Then I set up lookup filters to only look for previous invoices that matched either the name of the Exporter Company on the current Certification Invoice, or the name of the Third Party Company on the current Certification Invoice, with the default being the Exporter Company name if that was filled in.  In addition, the Certification Date+Time stamp of the current Certification Invoice must be greater than the Certification Date+Time stamp of the Previous Invoices.  That looks like this:

 
Previous Invoice Lookup filters
 
This leaves me with a lookup list of invoices for the Exporter Company or 3rd Party Company ONLY, and once I pick which one from the list is the most current one, the Remaining Balance value from that Previous Invoice drops into the Previous Balance field of the current Certification Invoice.

QUESTION 1: My lookup filters have narrowed down the field considerably, but I'd still like to have the Previous Invoice specify not ALL of the previous invoices for the current Exporter Company or 3rd Party Company, but the most current one ONLY.  I've changed the Certification Invoice # to be a straight auto-generated #, and the Certification Date+Time stamp is also auto-generated.  How could I add another lookup filter that would do this?  Or is there another way?

QUESTION 2: How can I track the history of invoices for a particular company and put this information on this Certification Invoice page layout, specific to the Export Company or Third Party Company listed on this invoice?

QUESTION 3: Could certification history for the current invoice company be listed in the standard Related List called Certification Invoices?  If so, how would I set that up?  I've set up columns with relevant fields in the Related List section for Certification Invoices, but I don't know how to relate these fields to the previous invoice data.  Here's how it currently looks:
Related Lists screenshot
Cynthia DouglassCynthia Douglass
Hello everyone, I wanted to let you know that this question has been solved, through setting up a series of process builder that find the latest invoice for a company, and by creating a lookup relationship for the Certification Invoice with Accounts to display invoice history.  

Premier Support got involved and finally solved it, but the ideas came from you.  Thank you so much for all of your inspiration and assistance!

Best,
Cynthia