By using this website, you agree to our Terms of Use (click here)
The GI I'm currently working on requires info from both the Service Order and the Appointment (and Sales Order). When I joined them using SOOrder Inner FSAppointment with soRefNbr = soRefNbr and SOOrder Inner FSServiceOrder with orderNbr = refNbr, it didn't return all my Sales Orders. If I change it to a left join then I get all my Sales Orders without all the Service Orders. What I discovered was that it only finds the Service Orders created from the Sales Order. If the Service Order was created first and the "Generate Invoices from Appointment" process is used to create the Sales Order (IN), then the join doesn't find the Service Order associated with the Sales Order. I understand that it is possible to have many Sales Orders per Service Order since there can be one for each appointment so I know Sales Order needs to be joined with FSAppointment.
Does anyone know how to join Sales Orders and Service Orders that were created this way?
I have an answer now. This works for Sales Orders created from Appointments. I linked them as follows.
SOOrder Left SOLine using orderNbr = orderNbr
SOLine Left FSAppointment using AppointmentID = appointmentID
FSAppointment Inner FSServiceOrder using soRefNbr = refNbr
Hi Alisann,
Thanks for posting your answer.
I don't know the Service Order module very well, but I just checked the primary keys on those tables and it looks like there are two fields needed to form the primary key on each:
FSAppointment: SrvOrdType and RefNbr
FSServiceOrder: SrvOrdType and RefNbr
SOOrder: OrderType and OrderNbr
You might want to include the Type columns in your joins, not just the Nbr columns just in case you wind up with the same number getting used across multiple types (it's possible).
Online Members
