Default value in a look-up table

[AndrewMozley]

Trying to work out the best way of handling this.

I have a table of currencies, which contains currency_identifier (e.g. EUR), a description (e.g. Euros), an exchange rate (1.42) and a few other things, like the code for a nominal account to which gains / losses on that currency are to be posted.

All installations have a default currency, which is most commonly £ - Sterling - but it does not have to be.

Several tables, like the customer and supplier tables, have a reference to this table in each record - so, there may be suppliers who present their invoices in Euros. For the form which maintains such a table, there is a combo box which allows the user to select a currency; this offers all currencies, including the home currency.

I had rather wanted the currency_identifier to be blank for the home currency. So, on several screens I only wish to display the currency code if it is foreign.

Coming back to the currency table, XCURR.DBF, I had tried to ensure that the currency codes are unique by making the currency identifier (EUR &c) a candidate key. But this creates a problem: when the user wants to create a new currency, I would normally append a blank record to XCURR, start a transaction and then display a form with several text boxes bound to control sources in the XCURR table, until the user says Save or Undo.

But (as I have it currently set up) this causes a problem when I try to APPEND the blank record for editing, since a record with a blank currency-identifier already exists.

What I currently plan is to make the currency_identifier key just a regular index - then when the user clicks on ‘Save, check for a duplicate in that field.

Feel I am making rather heavy weather of this – can anyone think of a better approach - thanks, Andrew.

 

[Olaf Doschke]

Don't worry, I'll get over it.

I'm still a bit baffled, because I have used candidate indexes for the same reason as Andrew. I don't remember having stumbled across this CDX behaviour. It might be a hint on the almost guaranteed index corruptions in conjunction with oplocks. I know CDXes are a bit special, eg if you CREATE CURSOR the function DBF() points to a non exiting file. It is in memory and will be created, when the cursor content overflows cache memmory, but the moment you index a cursor field the CDX file will be created on hdd. So it seems cdxes are more instant also in that respect.

I'll have to experiment, if the index node on the buffered row really changes the CDX file or if that CDX change only is in memory.

Having slept a night over the FOR RECNO()>0 condition I'm still not definitely recommending it. But I'd do something to regain the freedom of moving around the record pointer in table buffered DBFs or display them in a grid or browse. All this is hard with the index update on leaving a record. INDEXSEEK with the option to not move to the found record helps a bit, but for the price of freedom. If you have that restriction you also could do record buffering only.

What surely helps would be taking the DBF handling one step further and use views or better cursoradapters. Then you don't have the candidate index on the cursor of the view or cursoradapter, you change and add data in the buffered cursor and only can violate an index at TABLEUPDATE()s. So the candidate index problem adds another reason to not work with DBFs directly, but with the one or other incarnation of queries on them into updatable cursors.

You initially do a lot of stuff when going this 2 or 3 tier architecture, eg you have to index the cursors every time, they don't inherit them from the DBFs. What helps a bit here is, that the majority of indexes is for query optimiazation and not needed in the result. You also don't profit from a candidate index on a cursor, as the cursor normally only contains partial data and the only place the candidate rule check makes sense is in the DBF. I live with all this additional "burdens" of client/server n-tier approaches for quite long as my development is with MSSQL Server backends, which enforces all the additional hoops you have to take when loading data from the server, but I started with DBFs and buffering and did that for about 10 years, too.

Bye, Olaf.

 

[mjcmkrsr]

Hi Olaf,

I don't remember having stumbled across this CDX behaviour.

Well I do. And I use this trick to circumvent it

lcNewCurrency = "YEN"
lcDescription = "Japanese Yen"

If Indexseek(lcNewCurrency,.F.,"currencies","xCode")
   MessageBox(lcNewCurrency+" already exists")
Else
   Append Blank in currencies
   Replace cCode with lcNewCurrency, cDescription With lcDescription in currencies
   
   If TableUpdate(0,.F.,"currencies")
      MessageBox(lcNewCurrency+" was stored")
   Else
      MessageBox("Storing "+lcNewCurrency+" caused some conflict.")
      = TableRevert(.F.,"Currencies")
   Endif
EndIf

hth

MK

 

[Olaf Doschke]

You're saying you only add the data to the table after being entered to non bound fields? Very limited approach. If yoiu do that, do an INSERT instead of APPEN BLANK + REPLACE.
I really like data editing forms to work with both exisintg and new records in bound controls, and that's not working with creating the record after entering it.

You should really look into cursoradapters.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

You're saying you only add the data to the table after being entered to non bound fields?

I'm not saying that - I'm saying that in this particular case, when you have to check for an existing record, you might want to use either INDEXSEEK() with lMovePointer = .F. or the above approach, if for whatever reason you would prefer the SEEK() function or the SEEK command.

MK

 

[Olaf Doschke]

I just said I prefer to move freely around in a table buffered DBF, which the CDX behaviour does not allow.

Using SEEK or LOCATE or GOTO or moving in a BROWSE or GRID or checking rules for a group of records also by SCAN...ENDSCAN. All that should be possible in a table buffered DBF without failing on a candiate index in my opinion, I'm not just talking about detecting double data in regard of that freedom, but any other reasons to move around in the data. The restriction to not be able to move away from a new record until it satisfies the index rules is a too narrow restriction.

A solution is to use cursoradapter, as that not only adds the buffer but a whole other workarea and its buffer. It's double buffering.

Bye, Olaf.