SVO Profitability Data on GI

Hi ​@MFarnsworth10 ,

You can find the names of the Data Access Classes and fields by choosing Customization-->Inspect Element and clicking on the fields or column headers on the relevant forms.  In this case, I think the DAC that you are looking for is FSProfitability, with fields CuryExtCost, CuryBillableAmount, and CuryProfit.

CuryBillableAmount may be the billable amount, not necessarily the amount that has already been billed.

Hope this helps!

Laura

When I added the FSProfitability table, I was able to add those fields to my GI grid results but get a warning that those fields cannot be used  because the table is not joined with other tables on the relations tab.  What do I need to do there?

Hi ​@MFarnsworth10 ,

Normally you would need to join the additional DAC on the relations tab.

However, after looking into this further, this particular table (FSProfitability) does not actually exist in the DB and therefore it can’t be used in a GI.

See this community thread:

As per that thread, your best bet will be to join the service order to the appointment(s).  The joins on the relation tab should be FSServiceOrder.SrvOrdType = FSAppointmnet.SrvOrdType and FSServiceOrder.RefNbr = FSAppointment.SORefNbr

Then you can calculate the profitability similar to the following:

= ( [FSServiceOrder.CuryDocTotal] - Sum( [FSAppointment.CostTotal] ) * 100 / [FSServiceOrder.CuryDocTotal]

Hope this helps!

Laura

Will the FSProfitability table every be added to the DB?  Seems like it would be much easier since I still cannot seem to find Service Order Total Costs that will populate. 

I was able to get the joins on the relation tab as discussed above, but when I tried to do the calculation for profitability I get an error…

And I could use the sum of the Appointment Costs, but they each show up on separate lines right now…

Thank you so much for all of this help.  We are really close, but now that we are grouping if you look back at my example above with Joe Whitters everything appears on one line which is perfect, but the Billable Total is doubled up from adding the two lines together.  (The costs from the appointment lines need to be added together, but not the billing from the service order.)  Also, how do I get the profit columns to round to 2 decimal places?

The one correction I would make to this would be - 

= ( [FSServiceOrder.CuryDocTotal] - Sum( [FSAppointment.CostTotal] ) * 100 / [FSServiceOrder.CuryDocTotal]

If [FSServiceOrder.CuryDocTotal] could be empty you want it to be 1 so you need to do an IIF formula for this.

Hi ​@MFarnsworth10 ,

For the Billing Total function you can use the MAX function.  Since both are the same, MAX or MIN should give you what you need. 

To set the formatting to two decimal places, set the Schema field on the formula field to one of the standard fields that matches the format you want.

Hope this helps!

Laura

Exactly where do I make this change?  I keep getting errors when I view it.

Thank you lauraj46 I think I finally got what I needed.  Appreciate it.

So this GI now works for SVO’s that have appointments, but we have some that do not have appointments.  How do I get the costs for those because the costs are on the SVO?  Do I need a sperate GI for that?  Can you get the costs off the SVO?

Hi ​@MFarnsworth10 ,

You can use a LEFT join from FSServiceOrder to FSAppointment to include service orders that don’t have any related appointments.

The field FSServiceOrder.CostTotal seems to be what you are looking for on the service orders, but I would recommend spot checking a few to make sure this is what you need.

This formula will return the FSServiceOrder.CostTotal (if there are no related appointments), or the sum of the FSAppointment.CostTotal (if there are related appointments):

=iif(Count([FSAppointment.AppointmentID])=0, max([FSServiceOrder.CostTotal]), sum([FSAppointment.CostTotal] ))

Hope this helps!

Laura

This did work for the Cost Total, now I see it whether it is the sum of the appointments or the service order, but the Profit and Profit % calculations only work for the lines that have Appointment Costs.  How do I change my profit formula to reference either the appointment or service cost totals?