Combining 3 fields into 1

[woodyinoz]

I am looking to take 3 fields and put them into one field. For example, I have a contract# a structure# and a plate# and I want to put them together to create a unique id
i.e.
Cont# = 804
str# = 443
plat# = 10

new field = 80444310

The strucure# and plate# could be anything from 1 to 999, whilst the contract# is always 3 figures although it may increase to 4 at a later date. The values of all 3 fields are integers.

Thanks,

Woody

 

[footpad]

Woody,

There are several ways to do this.

For example, you can use a QBE query:

var
   qry       Query
endVar

   qry = Query

      Table1.db | Cont#    | Str#     | 
         check  | check _c | check _s | 
         
      Table1.db | Plt#     |
                | check _p, calc ~( format( "W4,EZ", _c ) + 
                                    format( "W4,EZ", _s ) + 
                                    format( "W2,EZ", _p ) ) as NewID |
         
   endQuery

(Please note that three lines containing the format statements need to be placed on the same line of code. Be sure to do that if you copy this using the Clipboard.)

Now that's going to be really hard to read, but the basic idea is to save each of the three values into example elements and then use a query expression to format and combine each element into a zero padded string value.

Now, you'll see that the new field appears at the end of the Answer table. I'd immediately rename Answer to something useful and then restructure it to move the new ID to the first field so it can be keyed.

You can also do this using SQL queries or event ObjectPAL. A lot depends on how frequently you wish to do this.

If it's a one shot, then a query is probably the fastest way.

Hope this helps...

-- Lance

 

[woodyinoz]

Lance,

I have tried to use the code that you have given me but I come up with the following error when tying to close the coding window:-

_c is highlighted in the formulae and the error message "unkown identifier" is shown at the bottom of the screen.

Any ideas?

 

[footpad]

Woody,

Uknown identifier *usually* means, in this case, that either _c has not been defined *or* you're referring to an undefined variable.

Forgive me, but:

1) Are you doing this in a query window? If so, be sure to translate the ObjectPAL very I provided into an appropriate QBE version. Case in point: QBE windows don't understand VAR...BLOCKS.

2) Are you doing this in a script or event? If so, please check:

a) qry is actually defined.

b) That the three lposted lines containing format statements are, in fact, entered one one single with no carriage returns. (Sorry, but went for visibility and printability, not syntax accuracy. It works on this end, though).

c) Try enabling Compile with Debug and Compiler Warnings to determine which line of code is farkling.

If you still have troubles, please ZIP up a subset of the files and email to the address displays (in spoofed form) on my profile.

Hope this helps...

-- Lance

 

[footpad]

Woody,

Thanks for the files. I sent a more extensive reply via email, but wanted to post a brief summary for the thread.

I'm not sure why that error is occuring; it didn't show up when I mocked up a similar example using the sample tables. I suspect we were using different versions of Paradox at that point.

In any event, it may be better to use a more direct approach.

If we presume that a NewID field has already been added to the table (using Restructure), then here's a different way to do this:

method pushButton(var eventInfo Event)

var
   str  String
   tc   TCursor
endVar

   tc.open( "MatMan.DB" )
   tc.edit()
   str = string( tc.NRecords() )

   scan tc :
      Message( "Combining fields...Record " +
               string( tc.RecNo() ) + " of " + str + "..." )

      tc."NewID" = format( "W4,EZ", tc."Cont_" ) +
                   format( "W4,EZ", tc."Str_" ) +
                   format( "W4,EZ", tc."Material_" )
      tc.postRecord()
   endScan

   tc.endEdit()
   tc.close()
   beep()
   Message( "Done!" )

endmethod

As I mentioned in my private reply, this has little error-checking, however, it illustrates the basics. At the very least, you'll want to add errorTrapOnWarnings( Yes ) and save the form with Compile With Debug enabled.

This type of approach will run more quickly than a query based one.

Hope this helps...

-- Lance