Lookup table in PARADOX PAL 4.x DOS

[M.Emmanuel]

I would like to know how to implement a lookup table programmatically in PAL (PARADOX 4.x DOS)

In a report I would link the table via EXCHANGEMONTH and EXCHANGEYEAR fields and then use [LR001000->EXCHANGERATE]

But I do not know how to set up such link in PAL.

Thanks,

This is so far what I have:

EDIT "LR000001"

????? HOW DO I LINK LR001000 TO LR000001 TABLE?

SCAN

  ???? [EXCHANGERATE] = [LR001000->EXCHANGERATE] ????

  [EOYBALANCEACHF] = IIF([RUBRIK]="A", [EOYBALANCE]*[EXCHANGERATE], 0)
  [EOYBALANCEBCHF] = IIF([RUBRIK]="B", [EOYBALANCE]*[EXCHANGERATE], 0)
  [EOYBALANCECHF] = [EOYBALANCE]*[EXCHANGERATE]
 
ENDSCAN

LR000001 CONTAINS:
  [EXCHANGEYEAR]    <- populated manually
  [EXCHANGEMONTH]   <- populated manually
  [EXCHANGERATE]    <- needs to be retrieved from table LR001000

LR001000 CONTAINS:
  [EXCHANGEYEAR]    <- populated manually
  [EXCHANGEMONTH]   <- populated manually
  [EXCHANGERATE]    <- populated manually

[M.Emmanuel]

The message did not get any answer,  so I’ll try again with a simplified but hopefully clearer example :


QUERY

 ITEMS | CURRENCY  | FXYEAR   | FXMONTH  | CATEGORY  | VALUE     |
       | _CURRENCY | _FXYEAR  | _FXMONTH | CheckPlus | CheckPlus |

 ITEMS | RATE      | VALUEACHF | VALUEBCHF | VALUECHF  |
       | CheckPlus | CheckPlus | CheckPlus | CheckPlus |

 FOREX | CURRENCY  | FXYEAR  | FXMONTH  | RATE      |
       | _CURRENCY | _FXYEAR | _FXMONTH | CheckPlus |

ENDQUERY
Do_It!
CLEARALL

VIEW "ANSWER"
EDIT "ITEMS"
SCAN

  [RATE] = [ANSWER->RATE-1]
  [VALUEACHF] = IIF([CATEGORY]="A", [VALUE]*[RATE], 0)
  [VALUEBCHF] = IIF([CATEGORY]="B", [VALUE]*[RATE], 0)
  [VALUECHF]  = [VALUE]*[RATE]

ENDSCAN
Do_It!

The idea is that, as described, ITEMS already contains all the fields, but we want to populate RATE, VALUEACHF, VALUEBCHF, and VALUECHF using a script that looks up values from the FOREX table.
If anyone can correct the script above or provide the idiomatic way of doing this in Paradox 4.x for DOS, I’d really appreciate it.

Thanks.

[Steve Green]

sorry that nobody has responded.. I don't have time for a detailed reply right now, but two things

1. never use Edit Mode, always use CoEdit Mode

2. if I understand your situation, I would Scan the data table, and for each record Locate the lookup record you need, to get the extra field to pass back to the data table

--

Steven Green
Myrtle Beach, South Carolina USA

http://www.OasisTradingPost.com

Collectibles and Memorabilia
Vintage Lego Sets and Parts
- and Paradox support, too

--
You received this message because you are subscribed to the Google Groups "TheDBCommunity" group.
To unsubscribe from this group and stop receiving emails from it, send an email to thedbcommunit...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/thedbcommunity/aa124e93-bcd8-47e4-b792-2d870e6f5b41n%40googlegroups.com.

[M.Emmanuel]

Thanks for your answer.

I do understand this is a small community and the question is both niche and legacy.

Just in case you can remember more details on PAL, when you say:

"and for each record Locate the lookup record you need "

How do you actually locate the lookup record you need inside the SCAN? 

Any hint is appreciated since documentation is scarce and the books I have checked in archive.org do not cover this case.

[Michael Kennedy]

Apologies from me too - I just saw your original message now...

Maybe this approach would work...

View "Forex"
CoEdit "Items"

Scan

   Extract your key fields to temp vars, to search Forex...

   Moveto "Forex"

   Set Rate_Temp = 0 or 0.0

   Locate the required record in Forex

   If Found, Set Rate_Temp = [Rate]

   MoveTo "Items"

   Do your calcs, using Rate_Temp

   If any changes made, issue PostRecord. If that failed... handle locked files, etc...

EndScan

Do_It!  ClearAll

Mike

[M.Emmanuel]

Thanks for your answer.

I'm not entirely sure I’ve grasped the full procedure, so let me try to summarize:

1. MOVETO to select FOREX as the active image:
If I understood correctly, since both tables are opened via VIEW and COEDIT, we can switch between them during the SCAN using MOVETO.

2. FOREX table has the following structure:

CURRENCY *
FXYEAR   *
FXMONTH  *
RATE

(With the first three fields marked as keys)

So, for each record in ITEMS, we want to look up the matching RATE using those three keys.

3. Lookup values are stored in temp vars:
Let’s say we store CURRENCY, FXYEAR, and FXMONTH into temporary variables like TEMP_CURRENCY, TEMP_FXYEAR, TEMP_FXMONTH.

What I'm still unsure about is:
→ How do I use LOCATE inside the SCAN to extract RATE from FOREX using those keys?
I’ve looked through the PAL 4.0 reference manual, but I’m still not quite sure how this would be done in practice. I’m sure it’s something basic, but PAL predates the internet — and unfortunately, examples or working snippets are nearly impossible to find nowadays 

---

Side Note: How I Solved It

I ended up using a different approach based on queries:

1. I created a QUERY joining ITEMS and FOREX on the key fields. ITEMS contain all fields, both the source ones and the calculated or lookup.

2. Since RATE exists in both tables, the one from FOREX is automatically renamed to RATE-1 in ANSWER table generated by the query.

3. In a SCAN over the resulting ANSWER, I copied [RATE-1] into [RATE], and performed all the calculations (e.g. VALUECHF = VALUE * RATE, etc.). 

4. Then I made a new QUERY over ANSWER selecting all fields. This automatically discards RATE-1 (which is no longer needed).

5. I emptied the ITEMS table.

6. I appended all records from ANSWER into ITEMS.

This works reliably and is easy to follow. But I'm still very curious how the above MOVETO + LOCATE approach would be written idiomatically in PAL — any example particlarly for the LOCATE sentence would be still appreciated.

[Steven Green]

do exactly what Michael said

[Michael Kennedy]

Thanks, Steve!  ;-)

----------------------------------

Few comments to the OP...

I'm assuming you're using PDox 4.5 - it's major upgrade from 4.0. And, you should be using 4.5 docs also.

Your approaches suggest your app is single-user - if it's multi-user, it should be reviewed!

And you don't mention restrictions on processing time, nor on storage space, nor on table sizes.

Your 3-step summary is correct.

Use Locate  TEMP_CURRENCY, TEMP_FXYEAR, TEMP_FXMONTH (in PDox 4.5). And then check RetVal!!

In all your approaches, you must build in checks for every I-O function failing, so that, when things go wrong, the app will fail gracefully, and tell the user exactly what's wrong, and maybe suggestions on solutions. Eg, sooner or later, tables will be missing, tables will be corrupt, tables won't match, the app won't have enough storage space, the app will lose write-permission to tables,  etc, etc, etc...

Oh, you probably could use a "ChangeTo" query, but you'll still have to cover situations like mis-matching tables, etc, etc, etc.

 - Mike