Two Tables, One Report, One Search Form

[kpcollier]

I've got a confusing problem here.

This is for an Orders App. Our company creates an order on the submission page, the record is inserted into the Ordering_Table. This record stays on this table until we receive it. Once the order is received, it is copied over to the Ordering_CompletedOrders table, and deleted from the Ordering_Table. So, one table is for Orders we have placed but not yet received, and the other is for orders we have received.

Each order is tied to a Job. On the submission form, the first box is a dropdown to select a Job from the list. This dropdown is populated from a separate Job_Table. So, if I search through orders by job, I should be able to see all of the orders in that table that share the same Job. This is where the problem starts.

I am trying to create a report datapage that shows ALL orders (received and not received) per Job. However, I am trying to avoid having "Ordering_Table_Job" and "Ordering_CompletedOrders_Job" in the search form, and would rather just have one "Job" search criteria that would pull records from both tables. 

Any help would be appreciated.

[Corpcat]

On 3/28/2019 at 8:37 AM, kpcollier said:

I've got a confusing problem here.

This is for an Orders App. Our company creates an order on the submission page, the record is inserted into the Ordering_Table. This record stays on this table until we receive it. Once the order is received, it is copied over to the Ordering_CompletedOrders table, and deleted from the Ordering_Table. So, one table is for Orders we have placed but not yet received, and the other is for orders we have received.

Each order is tied to a Job. On the submission form, the first box is a dropdown to select a Job from the list. This dropdown is populated from a separate Job_Table. So, if I search through orders by job, I should be able to see all of the orders in that table that share the same Job. This is where the problem starts.

I am trying to create a report datapage that shows ALL orders (received and not received) per Job. However, I am trying to avoid having "Ordering_Table_Job" and "Ordering_CompletedOrders_Job" in the search form, and would rather just have one "Job" search criteria that would pull records from both tables. 

Any help would be appreciated.

I would have had both the orders and the completed orders in the one table with a checkbox when completed and a filtered view of the table that way you dont have to do the input and delete function and you can run a report with collapse-able grouping on the checkbox field.

the way you have it done it you can embed two filtered reports into your datapage, one for the ordering_table_job table and one for the Ordering_completedOrders_Job.

 

 

 

[kpcollier]

@Corpcat Thanks for the reply! I originally had it all on one table. However, I came across a couple of problems. I can't remember them all at the moment, but one thing I was afraid of is having the table have so much data. I built this app quite a while ago when I was still fresh to it all, and had normalization in mind. This app has only been live for 3 days and our company already has ~100 records in the Ordering_Table_Test and ~30 in the CompletedOrders table. Idk if this really is a problem to look at, but I'm afraid it is too late to go back, now. We are now trying to tie in CompletedOrders table with inventory, and that part is working well.

 

[Corpcat]

On 3/30/2019 at 4:07 AM, kpcollier said:

@Corpcat Thanks for the reply! I originally had it all on one table. However, I came across a couple of problems. I can't remember them all at the moment, but one thing I was afraid of is having the table have so much data. I built this app quite a while ago when I was still fresh to it all, and had normalization in mind. This app has only been live for 3 days and our company already has ~100 records in the Ordering_Table_Test and ~30 in the CompletedOrders table. Idk if this really is a problem to look at, but I'm afraid it is too late to go back, now. We are now trying to tie in CompletedOrders table with inventory, and that part is working well.

 

So you might end up with 9000 records in that table over a year - my main table has 365000 records, so I dont think it will cause a problem for performance.

Ok I think you should create a view that links the two tables together using the Job number - then you can create two reports from the one view.

The first will show the Orders not completed, by selecting only the fields form the Ordering table and the 2nd will show the Completed orders.

Then embed both tables into a details page which passes the Job number as a parameter to both embedded tables. 

 

 

 

 

[kpcollier]

Thanks, @Corpcat. I will try this out and get back to the post with how it went.