Table relationships and direct access to matching records

lugoimports · November 20, 2011

I want to create 3 tables. One with the customers purchase order, another one with the job or jobs associated with the purchase order. Another table with exceptions/issues related to each job. FIrst, I want to be able to show my customer their PO list. Included in list, I want to show a count of how many jobs are associated with PO. For example, PO # 12345 can have JOB1, JOB2 and JOB3. The customer will only see the number 3 and if you click on it, it would take you to another report listing the individual jobs. Same thing for exceptions. Each job may have multiple issues, for example "MISSING PRODUCT", "WRONG SIZES", etc. I want the customer to look at the job, see an exception count and be able to click on it to see a detail table. Help! BTW, I understand the concept of building table relationships. What I need are the actual steps/code to take it happen.

Report 1

PO JOBS

12345 3

"here I click on number 3 and see the following report:

Report 2

JOB EXCEPTIONS

JOB1 3

JOB2 1

JOB3 0

Here, I click on number 2 for JOB1

TABLE 3

EXCEPTION

MISSING PRODUCT

WRONG SIZE

INCOMPLETE PRODUCT

MayMusic · December 8, 2011

You can simply do it by passing parameters. All you need is to include the poID in job table and JobID in exceptions table. In the first report which is based on orders table you can add an HTML block and create a link to send out the poID to next page to show the related jobs. For instance:

Show Jobs

Then you can create a pre-defined criteria report based on Job table and filter by receiving poID to show related jobs to each order. You can do the same thing for a pre-defined report based on exceptions table.

Here are some tutorials that can help you to create your reports:

http://howto.caspio.com/parameters/in-a ... pages.html

http://howto.caspio.com/parameters/pass ... tring.html

http://howto.caspio.com/datapages/form- ... locks.html