Looking for input and ideas

[kfenner]

Ok, I told you I would probably think of some other questions. These are more "looking for ideas" questions rather than technical ones. I am going to try to put this all in a nutshell, but may be hard, so please, try to be patient.

The "application" I have been working on is an attempt to alleviate my office's use of a massive quantity of "lists" for client data. I created a "client" table and a "contacts" table. "Client" is keyed on the "client" field and "contacts" is keyed on Name and address. They are linked one to many on the "client" field with the "client" table being the parent table. The "notes" field in the "client" table utilizes it's own form, using a setrange of the value of the "client" field. Currently, we use a form document when a new case is opened. It is filled out, then printed and passed on to various people. When it returns, a "client number" is assigned to it. This number will signify the "client" field in my table.

In order to convince them to use this "application", rather than all the lists that are used, I need to make it automate the tasks we use to open files. I don't want everyone to have to fill out this current form we use, then turn around and have to input all this information again in the tables. Problem is, the form we use contains way more information than we need to have available in the tables I have developed, and you could never key it on the "client" number, since when it is initially filled out, it has no number and is left blank. So, if many are done in a day, you will get key violations.

I thought of setting up an independent table with all the fields that are used in the current form for people to fill out, then print and use as the "new case" form that is circulated. Then, when it returns, and a number is assigned, it can be filled in. On a weekly or bi-weekly basis, this table could be queried for only the necessary fields to then merge into the "application" I have developed. Problem I am encountering is that there are too many fields to fit on one page of a form, forms don't print will on our system, and multi page forms will only print the first page. You can't "publish" a form to another program such as WordPerfect of Word so that is not an option. I don't know how to begin to set up a query that would be automated on a button, to automatically create a printable report based only on the current record open. So, I need some ideas how I might be able to do this.

This has gotten way longer than I planned, so I will save my other question for later. Thanks for any input anyone has.

 

[footpad]

kfenner,

Wow. Lot's of issues. I'll try to provide some general guidance and links, but you're going to need to think some of this through and greak things into individual questions to get some really useful answers.

As you pointed out, the problem with keying tables by data fields is that you need the key assigned before you can add detail records and so forth.

Since your natural key is not assigned until later un the information cycle, you will either need to use arbitrary keys that are hidden from the end-user or assign temporary keys that are changed when the client receives a case number.

I've done both and there are benefits and risks to both.

As a general rule, I tend to use arbitrary integer keys that are hidden from the end user. I only display the fields that are "public" (e.g. the ones they care about) and then use secondary indexes (and key lookups) to provide the user with the browsing, and ranging tools they need.

For example, the Paradox sample tables use a number for a key (bad idea). In that scenario, I would hide the key, but create secondary indexes on the name fields so people could locate the records they're interested in.

As far as automating the work you're doing, you could use the data in the initial form to add records in the underlying tables before opening the browing form. The general logic would be something along these lines:

1. Validate the data in the collection form; make sure it'll post correctly into the table(s).

2. Open a TCursor on the underlying tables, insert a new record, and then assign the values of the field objects to the fields iof the TCursor.

3. Open the target form and restrict its view to the new record. (Alternatively, locate the new record using qLocate())

Here's some sample code, though not it's not production ready:

if not tc.open( "MYTABLE" ) then 
   errorShow( "Can''t Save Data", "Use [>>} for details." )
else
   tc.edit()
   tc.insertRecord()
   tc."Field1" = fldObj1.Value
   tc."Field2" = fldObj2.Value
   ; and so on
   tc.postRecord()
   tc.endEdit()

   if not frm.open( "MYFORM" ) then
      errorShow( "Can't Show Data", "Use [>>] for details." )
   else
      frm.keyField.setRange( tc."KeyField", tc."KeyField" )
   endIf

   ; do this; always
   if tc.isAssigned() then
      tc.close()
   endIf
endIf

Now, that's off the top of my head, so there may be typos. But, it shows the basic process.

Also, you can help your users with the list stuff by populating drop-downs and list boxes on the fly. You can find a fairly extensive tutorial at

http://techtricks.com/paradox/poplists.php

and there is a ZIP file containing examples using the sample tables.

As far as the problems with printing forms containing too much data, you should be printing reports instead. That lets you organize the data so it fits on the page. I have a few different tutorials on the site that show how to combine query results with reports to print just the selected data. See

http://techtricks.com/paradox/btntutor.php

and

http://techtricks.com/paradox/printone.php

for starters. A few of the other articles may also be useful.

As you've noticed, building a robust database application can get pretty intense, even for things that seem simple. This is not necessarily endemic to Paradox; I've seen the same thing happen with other database products as well. The key is to break things down into discrete tasks and then build them one at a time.

Hope this helps...

-- Lance

 

[kfenner]

Thank you. This information is helpful. I have read a few of these articles as well as some other ones I have found and it has given me some ideas to try. I'll let you know if I get any more specific questions. Just needed a little input, to head me in the right direction, and you have done that well. Thanks again.