Including data from relationship field in calculation
Hello - Trying to figure out the best way to handle this.
We are trying to set this up to manage job estimates and then convert them to working jobs. But before all that as we quote out a new job estimate we have the job estimate app reference separate workspaces with apps of customers and vehicles. The vehicles are tied to the customers through the relationships app.
For the vehicles app we want to create a calculation that will produce the Year make model of vehicle as well as the owners name. So would result in something like "1999 Jeep Cherokee - John Doe". Is this possible?
Also when we give out an estimate we want to have our sales rep start at the job estimate app. they click to add new and the first field is the vehicle thorugh a relationship app. So if the vehicle isn't in our system they have to add new and add vehicle through the vehicle app. Then the next field in the job estimate is the customer. This would have already been setup in the vehicle app because we would have added a customer there through the relationship field. My question is - when we crate a new job estimate and click through to add new vehicle (which requires us to tie it to a customer) how can we then auto populate the customer in the job estimate? Right now we are having to select the customer twice. We select them through adding the vehicle. then have to select them again when get back to job estimate because we need to tie that customer to that estimate.
Any ideas?
-
Hi Tim,
are year and model already stored in one field or in two fields in the vehicles app?
If in one field enter in the calculation field in Vehicles:@NameOfFieldWithValueYearAndModel + " - " + @All of NameOfFieldWithCustomerNameif in two fields enter:
@NameOfFieldWithValueYear + " " + NameOfFieldWithValueModel + " - " + @All of NameOfFieldWithCustomerNameTo your second question: It really depends on your app setup - and if you're a Podio Plus (or Premium) member. If you're you can use Podio Advanced Workflows and it can be done rather easy. Do you need a relationship to the customer via a relationship field (I think so) or is a calculation field with the customer name sufficient? How do you give out an estimate, so the sales rep knows that he should start; what's the trigger for a job estimate? Is there always only one vehicle linked to one job estimate or could it be several?
Maybe you can tell some more details. If you can't use Podio Advanced Workflows I'm afraid you have to select the customer twice (or only add his name in a calculation field).
Rainer
-
Sorry Rainer I did not see your response. My response was to Hamid.
For your response - I have the first part figured out. It's all in separate fields but I have a calculation thanks to Hamid.
To my second question - I am a premium account. The relationship to the customer would be good. I'd rather not have the name in a calculation. For now I just removed the customer name from job estimate and the name could be accessed by clicking through from the vehicle on the job estimate.
One vehicle could be linked to several jobs. We give out estimate by first getting the vehicle info then the customer info.
I also have another questuion I just noticed - In job estimate I have it referencing a service app that contains the various services we offer, the time takes to complete the service as well as the cost. Can i make it so if I select a service in job estimate then it will auto populate the price and estimated hours?
-
For example you could do (if this is possible for the way your company works):
In vehicles app create a category field "Action" with categories "Sleeping" and "Create Estimate" . Then create a workflow: On update, if field "Action" changes and future value is "Create Estimate " than create a new item in App Job Estimate.In the Job Estimate relation field to customer you can select the Vehicle customer field. The new Job item has in its Vehicle relation field the relation to the vehicle and in customer to the customer. If you want you can add a conctat field, enter the sales rep and let the workflow create a task for the sales rep to finish the entries in the new Job Estimate item. Than as last step let the flow set the Action category back to "sleeping" (with: update this item). It's all in one flow.
Can i make it so if I select a service in job estimate then it will auto populate the price and estimated hours?
Yes, in one ore several calculation fields. You can add one ore more services to one relationship field and than list them in calculation field:
- Service 1: 2 hrs | $150
- Service 2: 3 hrs | $ 200 TOTAL: 5 hrs | $ 350
You can show also it as a table. Or each service in an own calculation field. If you tell how you like it and wihich kind of data are stored in a service item, I could show you an example code.
Rainer
-
Can i make it so if I select a service in job estimate then it will auto populate the price and estimated hours?Yes, in one ore several calculation fields. You can add one ore more services to one relationship field and than list them in calculation field:
Service 1: 2 hrs | $150
Service 2: 3 hrs | $ 200 TOTAL: 5 hrs | $ 350
You can show also it as a table. Or each service in an own calculation field. If you tell how you like it and wihich kind of data are stored in a service item, I could show you an example code.__
I'm confused as to how this would layout sorry.
Here's what we are looking for. Int he service list app we have name of service which we will fill out with full make and model of description. For instance Compustar XYZ Remote Car Starter. We then have Estimated Time to Install. As well as estimated install price. It would be nice if we could make it so this is a calculation where we could set our price per hour somewhere once then it will autopopuplate price. Also we have option for cash and carry price.
We want it so when we select a service from job estimate that it will auto populate that services price and hours. We want it to do that individually for each service we add through the relationship.
We then would like a way to total it all. I believe have to do this through globiflow because we want to total it all based on a field of taxable or not. If yes add tax to calcualtion, if no no tax.
This will all then be fed through globiflow to create a pdf invoice.
DOes that help?
-
Ah, you use Globiflow, that makes some things easier (I'm a really great GlobiFlow-fan, it doubles the benefits of Podio).
All things you want to have are possible, but it's hard to give advice without seeing the apps, cause some of the things you want to have are a bit complicated.
In Service App you can have a Number field for estimated hrs and a number (or Money field) for price per hour, both can be multiplied in a calculation field "Price for this Service" :
@estimated hrs * @price per hourIf you link a Job Estimate to a service the price (and hours) can be taken into a calculation field in Job Estimate. But all Job estimates would have the same prices. The main problem is: If you change e.g. Price per hour in some month all existing (old) Job estimates will be updated too. A better way would be: If you link a service, GlobiFlow takes the values from the Service App and puts them into number fields in Job Estimate. But therefore you would need several number fields in Job Estimate (which can be made hidden if empty). That's feasible in a rather elegant way, but a bit too complicated to explain it here in details.
Therefore only a short overview how the app can be structúred. Just imagine you have one relation field where the Sales rep selects the services, let's say 3. In your Job Estimate app template you've "groups" of 4 fields for each service: Name (calculation field), estimated hrs (number field), price per hour (number field), price for this service. You need as many groups as related services are thinkable. When a service is linked, it's name will be taken in the Name-Calculation field. When all Services are added, the Sales Rep clicks an "Action button" (= category) "get service data". This starts Globiflow, which takes name after name from the Name-calculation fields, looks for the current estimated hours and the price per hour for this service, and puts these numbers into the number fields. The price calculation field calculates the price, but the Sales Rep can also manually change the variables est. hours and price per hour. If you offer e.g. 100 services, but normally there are not more then 5 booked to a Job Estimate, i would create max. 10 groups (so you have a reserve). If the Sales Rep. adds only 3 services, there are only shown the "groups of fields" for these 3 services in the app item, cause all fields are made "hidden if empty".
The options cash&carry/install and taxable/not taxable (also as field in Job Estimate?) can both be considered in the calculation for the price of a service and for total price. You can do that in Podio calculation fields with Javascript's IF-conditions: eg:
@categoryfieldTaxable == "yes" ? @price*1.19 : @price(19% is VAT here in Germany for most products and services).
Are they valid for the estimate in general or can it happen that you need a cash&carry price for Service A, but an install price for service B (same with taxable or not)? If you need them for each service you have to add them to the "group" in your template.
The Total Price for the estimate is just an addition of the Service-Price-calculation fields.
@field 1 + @field2 + @field3 ....The main decision you've to make is in my opinion if it matters, that older Estimates will be changed if you change your price per hour or not, If it doesn't matter the whole procedure is easier to set up.
Easiest way would be to do it is in one calculation field. A very fast way is to create calculation field in Service App named e.g. "Service info":@ServiceName + " | " + @EstimatedHours + " | " + @ServicePriceThan create a calculation field in Job Estimated:
var hrs = @Sum of EstimatedHours; var servPrice = @Sum of ServicePrice; var table = "Service | Est. hrs | Price \n" + "--- | --- | --- \n" + @All of Service info.join("\n"); var total = "**Total: | " + hrs + " | " + servPrice + "**"; table + "\n" + totalThis would give back a table with the last line total (bold).
You can also create an extra field for the total price of all service or for the total price of services plus other things in the estimate.
Rainer
rg@delos-consulting.com -
I need to make a calculation field, with one specific reference from a relationship field, but when i type @ in the calculation field the reference or relationship does not show, can anyone help me solving this, neither if i type @all of... the relationship does not show, the relationship is named Target Language(s)
Please sign in to leave a comment.
Comments
15 comments