Using date ranges across multiple tables

Question

Hello All,&nbsp; Long time user/lurker, first time poster here. I have a situation that I have not been able to find an answer for yet, and I was hoping the community could help.I have an app with two tables. Table 1 is a list of Discrepancies. Each Discrepancy has fields for Name, Machine Number, and Date. (15,500 discrepancies)Table 2 is a list of all Parts installed on each Machine over the last 20 years. Each entry has a Part Number, Serial Number, an installation date and (possibly) a removal date on each Machine. (Almost 1 million entries)Is there a way to take Table 1 data for Machine number and date, and then filter data from Table 2? So all parts installed on that Machine where the Discrepancy date is within the Part installation/removal date range?&nbsp;&nbsp;I have an idea I might just have to convert each of the three date fields (Discrepancy Date, Part Installed Date, and Part Removed Date) into new field where each day has a single sequential number (starting 01JAN2000 was Day #1).&nbsp;&nbsp;Ideally, the user would click on a Discrepancy on Table 1 and would be presented with a list of parts that were installed on that Machine on the date of the discrepancy.&nbsp;Please let me know if anyone has any ideas. Otherwise I'm stuck allocating labor hours to just manually searching and filtering to obtain this data, which is not ideal.Thanks!Lou------------------------------Louis Wambsganss------------------------------

evanmartinez · Answer

Hi Lou,I just have a clarifying questions Lou on the way your app is setup. Is your Discrepancy table and your Parts table related either directly or through another table? If those two tables are related it would be possible to use the relationships to pass down information like the Discrepancy date to use as a filter in a report. Then that report could be used in a report link on your Discrepancy table. Otherwise the data you would need to filter your Parts wouldn't have an easy way to pass down to the Parts table.------------------------------Evan MartinezCommunity Marketing ManagerQuick Base------------------------------

louiswambsganss · Answer

Evan,&nbsp; Thanks for the reply. The tables are currently separate. They are populated from separate .csv files. How would I build the necessary table relationships?------------------------------Louis Wambsganss------------------------------

evanmartinez · Answer

Hi Lou,If you were creating a relationship would it be the case that one Discrepancy can have many Parts but any one Part would only ever be related to one Discrepancy? Or is it possible that a single part record might tie back to multiple Discrepancies? Also is there any piece of data that appears on both that would be unique? Like an incident number or anything?------------------------------Evan MartinezCommunity Marketing ManagerQuick Base------------------------------

louiswambsganss · Answer

Evan,&nbsp; Again, thanks for your time. It is possible that one Part has multiple discrepancies, but one discrepancy will only affect one part. &nbsp;In my context, a Machine is a complex assembly made up of many parts. Each part is tracked by a Part #&nbsp;and Serial #. A Machine is constantly having parts removed and installed.&nbsp;The issue I have is that I cannot automate the assignment of a specific part (Part #&nbsp;and unique Serial #)&nbsp;to a discrepancy. Due to variables in how technicians describe issues, I cannot rely on automated sorting or filtering. A technician will create a discrepancy that just says "Machine #37 is broken". So I need to have humans read each discrepancy text and decide "OK, this is a problem with a Main Widget" or "This is a problem with a Secondary Pressure Switch" etc. as applicable.&nbsp;In order to help streamline this process, I'm looking for a way to link Part Installation data onto a form in the Discrepancy Table. My thought is that when a user views a Discrepancy, the form will take data from the Discrepancy Table (Discrepancy #, Machine #, and Discrepancy Date) and will display all Parts&nbsp;where Discrepancy Date falls between Part Installed Date and Part Removed Date, and Discrepancy Machine #&nbsp;equals Part Installed On Machine #&nbsp;&nbsp;​. So the User will be given a list of all parts that were on that machine on that date, and they can choose the correct part.​​​​&nbsp;Here are two example Discrepancies from the Discrepancy Table. I need to fill in the Part and Serial fields.

Discrepancy Table

Discrepancy
Machine
&nbsp;Discrepancy Date
Description
Discrepancy Part&nbsp;
Discrepancy Serial

1100
45
20-Jan-05
Widget broken
???
???

1540
37
1-Aug-14
Not working right
???
???

Here are two sample entries from the Parts Table:

Parts Table

Part #
Serial Number
Installed Date
Installed on Machine
Removed Date

12498
357A
12-Feb-04
45
16-Sep-09

25497
498B
23-Apr-19
37
(NULL, still installed)

&nbsp;In this case, I can see that Part 12498 (Serial 357A) was installed on Machine 45 at the time when Discrepancy 1100 was raised, so I would choose this part to fill in Discrepancy Part and Discrepancy Serial. I just need to be able to do this for 15,500 Discrepancy records, pulling from a Parts Table with nearly 1 Million records. Due to the large quantities, I'm trying to automate as much of the research as possible. Ideally, the human effort in this would be minimized to just interpreting "system hydraulic pressure fluctuates" and selecting "pump A", rather than manually researching what pump was on that machine at that date.------------------------------Louis Wambsganss------------------------------

markshnier__you · Answer

LouisI do know how to do this.My thought is that when a user views a Discrepancy, the form will take data from the Discrepancy Table (Discrepancy #, Machine #, and Discrepancy Date) and will display all Parts&nbsp;where Discrepancy Date falls between Part Installed Date and Part Removed Date, and Discrepancy Machine #&nbsp;equals Part Installed On Machine #&nbsp;&nbsp;​. So the User will be given a list of all parts that were on that machine on that date, and they can choose the correct part.​​​​I can help you one on one to implement a solution, but here would be the proposed workflow it this meets your need.The user views a Discrepancy and on that record click a button.&nbsp; The button will edit or add to a table of users called User Focus table where the key field is the Userids.&nbsp; The particulars of the Descrepancy record will&nbsp; looked up by a reverse relationship to the User Focus record.&nbsp; Then those fields will be looked up down to ALL Parts and Parts which meet some complex criteria will be displayed immediately on the Descrepancy record.&nbsp; The user can then click a button on the part record to select it whatever that means - presumably it means to add a record on a table somewhere or to edit the Descrepancy record to to assign the cause of a problem to a Part.&nbsp; That same button could also redirect to the original list of 15,500 Discrepancies&nbsp;to be coded, however now the report will "only" be 15,499 records long and you just put a bunch of staff onto it all hammerin' away until you beat that list down to zero records.So the goal is to minimize the clicks and make every one count since you are saying that a Human needs to have a quick look to interpret the comment and then assign the blame to a part.It's probably just an hour or two of one on one consulting time required.mark.shnier@gmail.com www.QuickBaseCoach.com------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

Forum Discussion

Using date ranges across multiple tables

Related Content

Using Grid Edit to Edit Multiple Records at Once

Edit Multiple Records at Once with Grid Edit

How to consolidate multiple rows into one

Modify multiple rows within the same group using Single Record ?

Building a report from multiple tables