Forum Discussion

FOZZY's avatar
FOZZY
Frequent Visitor
2 years ago

Connect Work orders to Invoices based on date proximity

Hello.

I need to connect work orders in table 1 to their corresponding Invoices in table 2 based on the date proximity from Work order submission and Invoice creation. The only thing they cave in common is the customer ID. There are multiple work orders and invoices for each customer. Does anyone know how I can do this?  I appreciate any input.  thanks!

Need Help

4 Replies

  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi FOZZY 

     

    You can create seperate table for Customer and connect invoices and work orders to that following way 

     

    Thanks,

    Sayali

     

    • FOZZY's avatar
      FOZZY
      Frequent Visitor
      2 years ago

      sayaliredij 

      Thanks for the response.  I might have used the wrong terminology in my request.  Once the Work Order is submitted, it takes a few days for an Invoice to be generated (so the WO and INV do not show the same date).  What I'd like to do is create a visual that shows the Customer ID, their Work Orders and the corresponding Invoice for each WO.  but each customer has multiple WO's and Invoices - all on varius dates.  I need a way to determine which Invoice goes with which WO based on the proximity of the dates submitted.  

       

      Examples...

       

      Work Order Table

      CustIDWO_DateWO_NoPartNoUOM
      G1/10/2024

      2007

      		REPAIRHR
      C11/15/20232001REPAIREA
      A1/9/20242005NOCHGHR
      D1/9/20242006LABORHR
      A11/30/20232002LABOREA
      C1/5/20242003REPAIREA
      B1/9/20242004REPAIREA

       

      Invoice Table

      CustIDInv_DateInv_NoPriceItemQty
      A12/5/20231001$0NOCHG1
      A1/13/20241005$0LABOR1
      B1/10/20241003$0LABOR2
      C11/20/20231004$0REPAIR1
      G1/13/20241006$0REPAIR1
      C1/10/20241002$77REPAIR2
      D1/13/20241007$0REPAIR2

       

      Combined Data

      CustIDInv_DateWO_NoInv_NoPriceItemQty
      A12/5/202320021001$0NOCHG1
      A1/13/202420051005$0LABOR1
      B1/10/202420041003$0LABOR2
      C11/20/202320011004$0REPAIR1
      G1/13/202420071006$0REPAIR1
      C1/10/202420031002$77REPAIR2
      D1/13/202420061007$0REPAIR2
  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    2 years ago

    HI FOZZY 

     

    Is there any relation for Item Name? in Invoice and work order? Should it have the same name ?

    eg. First Row from the combined data - it says that row belongs to NOCHG but if i check the work order - 2002 it says its LABOR?

     

    Thanks,

    Sayali

    • FOZZY's avatar
      FOZZY
      Frequent Visitor
      2 years ago

      sayaliredij 

      My apologies for the late response.  No.  The only connecting item is the customer ID.  Unless you can think of another way tod o this, is there a measure that I can write to somehow organize the WO's and INV's based on the closeness of dates on each?