Fields as columns possible?

[westexp]

I have a database of delivery tickets and a related database of customers. Every ticket shows delivery date, bldg delivered to, customer and number of widgets. If the above format is possible to generate, how do I go about setting that up?

I am new to FM and appreciate any help!

[Sanjai]

If you can give the field definition of your tables, it would be easier to explain. However, you can create a new table with fields which you want as rows in the report. Run a find script to get the foundset in the original table and then import the reoords in another table. The only drawback is the report would be single user.

[westexp]

Perhaps it would be easier if I describe the scenario:

We bill our Customers (there are only 7 customers) for storing widgets. We need to show them on a daily basis (we can receive many shipments on the same day) how many widgets were delivered (and to which building).

Does that help?

[Sanjai]

I am assuming that in the original table you have delvdate, bld, widgets, related field customer.

To create a report similar to the one shown in your first post, create a new table with the following fields:

DelvDt, Bldg, Cust1, Cust2..... Cust7

Create a layout in the original table to specify the date for the report. Write a script in the original table to do the following:

Find the records where delvdt = date entered by user AND customer=firstcustomer.

If there are matching records then run a script in a new table to import records from original table using the following field mapping.

delvdate from original table -----> dlvdate

Bldg from original table -----> Bldg

Widgets -----> Cust1

Next step:

In the original table, find the records where delvdt = date entered by user AND customer=second customer.

If there are matching records then run a script in a new table to import records from original table using the following field mapping.

delvdate from original table -----> dlvdate

Bldg from original table -----> Bldg

Widgets -----> Cust2

You would have to repeate the above steps for the seven customers.

Note: I am importing widgets field and not the customer name.

Create a list view layout in new table with the heading as displayed in your sample report. Doing show all records would display the information.

So if I have:

DelvDate

05/01/05 <3A> <5>

05/05/05 <3B> <6>

05/07/05 <4A> <2>

05/01/05 <3A> <7>

If the user searches for the month of May, the report would display:

DelvDate

05/01/05 <3A> <5>< ><7>

05/05/05 <3B> <6>< >< >

05/07/05 <4A> < ><2>< >