Connect Work orders to Invoices based on date proximity

[FOZZY]

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!

[sayaliredij]

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]

@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?  

[sayaliredij]

Hi @FOZZY 

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

Thanks,

Sayali

[FOZZY]

@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

CustID	WO_Date	WO_No	PartNo	UOM
G	1/10/2024	2007	REPAIR	HR
C	11/15/2023	2001	REPAIR	EA
A	1/9/2024	2005	NOCHG	HR
D	1/9/2024	2006	LABOR	HR
A	11/30/2023	2002	LABOR	EA
C	1/5/2024	2003	REPAIR	EA
B	1/9/2024	2004	REPAIR	EA

Invoice Table

CustID	Inv_Date	Inv_No	Price	Item	Qty
A	12/5/2023	1001	$0	NOCHG	1
A	1/13/2024	1005	$0	LABOR	1
B	1/10/2024	1003	$0	LABOR	2
C	11/20/2023	1004	$0	REPAIR	1
G	1/13/2024	1006	$0	REPAIR	1
C	1/10/2024	1002	$77	REPAIR	2
D	1/13/2024	1007	$0	REPAIR	2

Combined Data

CustID	Inv_Date	WO_No	Inv_No	Price	Item	Qty
A	12/5/2023	2002	1001	$0	NOCHG	1
A	1/13/2024	2005	1005	$0	LABOR	1
B	1/10/2024	2004	1003	$0	LABOR	2
C	11/20/2023	2001	1004	$0	REPAIR	1
G	1/13/2024	2007	1006	$0	REPAIR	1
C	1/10/2024	2003	1002	$77	REPAIR	2
D	1/13/2024	2006	1007	$0	REPAIR	2