Multi table form

[Lewy]

Hi all,

At last I am on the final? stage of my aplication. However as usual I have hit a brick wall. I have one master table and seven detail tables. I am trying to read data from one field in 4 different tables and place the info into it's respective field in a seperate detail table and show the values on the form.All the tables are keyed and linked via the data modeller. I have eventually managed using a TCursor to obtain the data but this has caused Paradox to crawl almost to a standstill. I am sure it should be possible to do without resorting to a TCursor. Any help as usual would be very appreciated.

Regards,

Lewy

 

[footpad]

Lewy,

It sounds like you'e currently making Paradox do a lot of work and I suspect that much of your slowness stems from the code you're using and where you've placed it.

For example, putting code into a setFocus() event is a bit risky because setFocus gets called everytime the object get's focus, including when you navigate to the field, return from a dialog box, return from switching applications (Alt+Tab), and so on.

Let's step back a bit and see if we can figure out why you needed the code in the first place. Did you run into problems when you placed your fields from the linked detail tables on the form? If so, what were those problems?

-- Lance

 

[Lewy]

Lance,

Many thanks for your reply. The code itself is on a push button. What I am trying to do is at the end of filling in details for a transaction, I have a final detail table which takes currency values from a unique field from each of 4 tables.

Field 1 is the cost of an eye examination, located in Customer table

Field 2 is the price of the spectacle frame, located in the Order table

Field 3 is the price of the right spectacle lens, located in the RightLens table

Field 4 is the price of the left spectacle lens, located in the LeftLens table

In the final detail table (PxOrder.db) I have the following
fields: OrderNo, I*
Materials, $
Fees, $
Tax, $
Total amount, $
where Materials = (Frame Price + Right Lens Price +
Left Lens Price)*0.36
and Fees = (Frame Price + Right Lens Price +
Left Lens Price)*0.64
and Tax = Materials*7/47.
What I want to do is press the pushbutton and the values are read from the fields and the resulting breakdown calculations are displayed and posted into the PxOreder table. To complicate things further, on pushing another button, the eye exam and lens price values needs to be zero and the Tax.value to be taken only on the frame price. ALternatively, a simple check box for Private or Insurance transaction may be easier.

I hope this makes sense to you as I am not sure it does to me<grin>.

PLEASE PLEASE HELP.

Many thanks, Lewy

 

[footpad]

Lewy,

I think a different approach might help. In applications of this type, I've typically used a single materials table (as opposed to your LeftLens, RightLen, Frame, and so on) using something along these lines:

   MaterialID     I*
   Description    A32
   Price          $

Your data might look like this:

   ID   Description   Price
   1    Left Lens     $10
   2    RightLens     $10
   3    Frame         $20
   4    UV Coating     $5

I'm sure your prices vary.

Then you create an ORDMATLS table for each material in the order, much like the LINEITEM table in the samples provided with Paradox. When designing your form, place ORDMATLS as a detail to the order and bind it to a table frame.

Next, place a calculated field below the table frame defined as a summary field that totals all the materials in the order. We need to name it, so right-click, choose Properties and then change it's name to something like fldTotalMatl.

Now, we need to add code to two events of your newly-minted calculated field:

1. In the newValue event, add:

if eventInfo.reason() = fieldValue then
      self.postAction( userAction )
   endIf

2. In the action event, add

if eventInfo.id() = userAction then
      
      if pxOrder.Total_Amount.Value <> self.Value then 
      
         if not isEdit() then
            pxOrder.Total_Amount.action( dataBeginEdit )
         endIf
         pxOrder.Total_Amount.Value = self.Value
      endIf
   endIf

This will update your Total Amount field whenever records are posted into ORDMATLS.

So, this gives us a straight value for the total materials. You can then add calculated fields that:

3. Calculate materials using

pxOrder.Total_Amount.Value * 0.36

(Call this fldMtls)
4. Calculate the fees using

pxOrder.Total_Amount.Value * 0.64

(Call this fldFees)
5. Calculate tax using

fldMtls.value * ( 7 / 47 )

(Call this fldTaxes)

6. You can calculate the final total with

fldMtls.value + fldFees.value + fldTaxes.value

.

7. Adapt steps 1 and 2 to put the final total into the table.

8. Finally, hide the calculated fields you don't need to see and uncheck the TabStop property of the ones that are visible. That should help prevent certain data entry problems.

Mind you, this is mostly off the top of my head, so you'll need to fuss with it a bit. But, the basic ideas should be sound.

(Plus, the beauty of all of this is that the updates will happen as you post edits to ORDMATLS, so no button should be required.)

Hope this helps...

-- Lance

 

[Lewy]

Hi Lance,

Thanks for the information, I have restructured the tables as you suggested and based the data model on the one by Mike Prestwood (from his book & Web site). However not only does it take forever to move between records!! How do I keep the line item table 'fixed' to the order table? - In the demo from Mike Prestwood you can only scroll through the records in the lineitem table frame for that particular order. On my form you can scroll through all the lineitem records regardless of the order. As you can see from the dates I have now been on this for many days %-(. I have studied Mike's form until blue in the face. Any help as ever much apreciated.
Thanks,

Lewy

 

[footpad]

Lewy,

Hm. Have you removed your previous code? Depending on how you've implemented things, that mnay be slowing you down. When Paradox was first being produced, I wrote a similar application and found two things:

1. The depth of the data model can adversely hamper performance. For example (using the sample tables), a form with the following data model:

CUSTOMER 
      +->> ORDERS 
           +->> LINEITEM
                +-> STOCK
                    +-> VENDORS

Will almost always be slower than something like this:

ORDERS --> CUSTOMER
 +->> LINEITEM
       +-> STOCK
       +-> VENDORS

Primarily because of the amount of work that Paradox needs to perform to honor interactive use.

Consider, for example, the first data model. Suppose you have a form with a grid showing twenty customers and then click the page down on the scroll bar. Paradox now needs to locate the 20 customers to show, find all their orders, find each item in every order, and then locate the corresponding information from stock and vendors.

That's a lot of work for a simple page down.

The second version of the data model reduces the load considerable by reducing an entirely layer to a single fetch.

This, BTW, is one of the first lessons of client server and remote (e.g. CGI) databases, you need to design your data models to accurately reflect the work that's needed.

(BTW, for those that remember that far back, that's primarily how Borland got the sample MAST application to vastly improve its performance between versions 1.0 and 4.5.)

2. You get better performance with small data models than you do with complex ones. In this case, you might review your current form and see if you can move some of its functionality into separate forms that you open on the fly.

If you use ranges, qLocate() and other indexed-based searching techniques, you can vastly improve the performance of the application, even though you lose a little bit of that for overhead (e.g. opening the form, setting the range, waiting for the user, responding accordingly, and so on).

Also, make sure you actually delete old methods that you're not using, especially if you've placed them on the form object. Form level events can actually execute twice (once for the dispatch and once for the backstop handling). While this isn't a big hit (if you're properly using the isPrefilter block), it does add up, expecially in a code intensive environment.

If you're using queries or filters to locate matching &quot;sub&quot; records, you shoudl replace those with tCursor or range based approaches, as those are far faster.

Hope this helps...

-- Lance

 

[Lewy]

Thanks Lance,

Some of that went over my head, but I had already altered my data model to the same as you have suggested. This has cured the problem of both speed and the scrolling of the lineitem table. However as seems to be the norm, as soon as I solve one problem another rears it's head. Whenever I try to input more than one lineitem in the 'lineitem' detail table band, the part number being from a seperate lookup table, I keep getting a key violation problem. The only way of unlocking the record is to delete it leaving me with one item in the lineitem table. I am sure it is to do with how the tables are linked but have faithfully copied the data model of Mike Preston's example to no avail.

Any suggestions anybody? Before my PC results in GBH!!

Many thanks and sorry to keep bothering you all.

Regards,

Lewy.

 

[footpad]

Lewy,

It depends on how you've keyed the LINEITEM table. If you're using OrderNo as a key field, you'll also need to add a second field to the key. When choosing the fields to comprise the key, remember that you have to pick the set of values that will be unique to each record.

In this case, OrderNo and StockNo are natural keys because it's highly likely that you won't want two LINEITEM records with the same combination of values in those two fields.

However, you may run into user problems later. For example suppose you're creating an order and entering items with this setup. First you enter the stock number for the first item (1550) and then you enter a second line item (Stock Number 1331).

When you post the second item, it &quot;flies away&quot; to the top of the line item because 1331 is a small number than 1550.
I've heard many users complain about this type of behavior.

Because of this, I tend to use arbitrary keys in my tables, meaning I add a single LongInt field and then define its value behind the scenes using a network-aware autoincrement routine.

In any event, you'll want to expand the definition of the primary key in LINEITEM by at least one field.

Also, you should be able to get out of a key viol state by pressing Alt+Backspace (the shortcut for Undo).

Hope this helps...

-- Lance

 

[Lewy]

Thanks Lance,

It just so happens that after reading the manual, I realised that this was the problem and added and autoincrementing field into the lineitem table. This appears to have done the trick. All I need to do now is look at ways of speeding the model up, as soon as I add a new order, it seems to be very slow at inserting a blank record, but hey ho! at least it works and I can now input over 6 months of orders.......

Once again many thanks and sorry to be a nuisance.

Lewy

 

[footpad]

Lewy,

No problem. It's a process most of us have gone through.

I would caution against using the native autoincrement field type, though, especially if you intend to have this used on a network.

Instead, it's best to use ObjectPAL to create a network-friendly ID. If you'd rather not create this yourself, RDA Wordlwide has a an addin that does it for you. There are several types of licenses, including a freeware version. For more information, please see

http://www.rdaworldwide.com/RDAprod.htm

and then search for AutoKey.


Hope this helps...

-- Lance

 

[Lewy]

Thanks Lance,

Good news, the Champagne is now flowing. I designed a new form from scratch, using the same data model and hey presto the system works at the speed I am happy with. Now it's just a case of inputting all the data AND then adding some more refinements and applications.

See you all soon no doubt.

Lewy