Help creating Temp table

[Raymond Lyons]

I need a good way to create a temp table with data from a one-to-many set where each temp table record needs to have selected fields (not all) from the parent, from the first record in the child and from the last record in the child (if there is more than one child record--and there will often be 3 or more). A slight added complication is that the needed fields from the child's 1st and last records are the same fields, but in the temp table I need them labeled something like "First_fld_X" and "Last_fld_X".

Any suggestions would be appreciated.

Ray Lyons

[Bill Parker]

Re: Help creating Temp table

I start out thinking of "a" way to solve a problem and then get more sophisticated as time permits. So here are first thoughts.

1. create 2 sets, 1 with 1-1 link to 1st child and 2nd with 1-1 link to last child. this gives you the child values you need.
2. Use <tbl>.copy() to copy selected fields from first set. child fields will be fld_x.
3. use post operation to post from 2nd set to result table. here you can easily post from fld_x to last_fld_x.

Bill.

[Raymond Lyons]

Re: Help creating Temp table

Bill, thanks as always. What you outline would clearly work except for one strange thing: I have discovered that somehow the record order in the child table is not the order in which they were entered. To me that sounds impossible but ... Each child record linked to a parent gets a number (in a field) from a parent field starting with 1. Before a second child is entered (later!) the parent field in question is bumped up by 1 so that the second child record should get a number 2 when it is created. And so on. BUT, when I looked the child records in record number order, for a good number of records a child 7 comes before that same child's #1. Seems impossible, but there it is. So I have to figure out what in the heck is going on. If anyone has a theory, I'm all ears.

Ray

[Mike Wilson]

Re: Help creating Temp table

R,

I wasn't sure if your need was a form based action or a larger operation. I took your need to be a one parent record event, and not a broader scope operation.

I attached a database with an example that performs what you described from a form based upon a 1:M set (Q_header:Quote). The database opens to the MainMenu and choose Quote. There is a button with your name on it. I operates off the current record in focus and writes to an Xdialog. I am sure you can convert this to write to a temp table if th this Form-based action fits your needs.

Hope this helps.

Mike W

[Raymond Lyons]

Re: Help creating Temp table

Mike, thanks. My opertion will be from a gloabl script, either using all parent records or a large subset. I think Bill's suggestion would work fine except for the problem I outlined in my reply to Bill (see above). I still have not gotten to the bottom of that one.

Ray

[Raymond Lyons]

Re: Help creating Temp table

Okay, I am happy to say I figured out why a record with a 7 could come before a record with a 6, and so on. It had to do with creating a new child record via copying the current one into blob and then "pasting" the blob into a new record, then bumping up "its" (ha, ha) number so that except for the number the records at that point were identical. Trouble is, I failed to notice until now that the record that had its number bumped was the older of the two records in terms of record # order (basically I was backing up the current record by copying it into a new record and then continuing to modify the older record as the "newer" one). That was no problem until I tried to use Bill's suggestion, above. Back to the drawing board!!

Ray

[Mike Wilson]

Re: Help creating Temp table

Raymond,
Thanks for the update. I can only imagine the mind energy required to unravel that one, oh, Boy!,

Mike W

[Raymond Lyons]

Re: Help creating Temp table

Raymond,
Thanks for the update. I can only imagine the mind energy required to unravel that one, oh, Boy!,

Mike W

Yes, and it was quite a task to re-order all the records so that record numbers were consistent with number field in the child records such that record numbers always increase as the numbers in the child fields increase. Got that done the first time and then the darned memo file was corrupt (why do I keep trying to use memo fields!!), so I had to do it all over more carefully. Anyway, now all is in proper order, memos are fine and I can pass the files back to the client tonight. Then I'll see if I can get Bill's suggestion to work--the 1st part does but I do not think I'll get to the rest of it tonight.

Ray

[Tim Kiebert]

Re: Help creating Temp table

Ray,

Try adding the child table a second time to the same set and set the link to match last.

You may then be able to do it in one copy operation.

[Raymond Lyons]

Re: Help creating Temp table

Tim,

Thanks. I'll give that a try, but since I have yet to get working files back to the client, it will have to wait until tomorrow.

Ray

[Raymond Lyons]

Re: Help creating Temp table

Ray,

Try adding the child table a second time to the same set and set the link to match last.

You may then be able to do it in one copy operation.

Tim,

Thanks again for this suggestion. Adding the table a second time (using an alias) looks like it will allow me to use <tbl>.copy() in one shot to do everything I need to do. This approach does create problems with the field rules of the child table that uses an alias, but because this set will never be used for anything except creating a temp table, I do not think the field rules will be a problem.

Ray

[Raymond Lyons]

Re: Help creating Temp table

Tim,

Thanks again for this suggestion. Adding the table a second time (using an alias) looks like it will allow me to use <tbl>.copy() in one shot to do everything I need to do....

Ray

Not so fast for Tim's suggestion. For a small test (4-5 fields) <tbl>.copy() worked with the result being that the field names for the fields coming from the alias table in the set had the last character of the field names dropped and in their places there were 0's (e.g., "rate" would become "rat0"). That would have worked fine for my purposes. However, for some strange reason (seemingly a bug) when I add the full compliment of fields (41) the result is quite different. What I get is a table with duplicate field names (no "0" replacing the last character of the field name, as happened with the small test). Interesting that I can even browse this table full of data, but I need unique names the the fields to be able to do anything interesting with them. Back to Bill's suggestion, I guess, though I am not sure that is going to do the job either.

Ray

[Raymond Lyons]

Re: Help creating Temp table

Bill Parker's suggestion:

1. create 2 sets, 1 with 1-1 link to 1st child and 2nd with 1-1 link to last child. this gives you the child values you need.
2. Use <tbl>.copy() to copy selected fields from first set. child fields will be fld_x.
3. use post operation to post from 2nd set to result table. here you can easily post from fld_x to last_fld_x.

After straightening out the record order (see above posts), Tim's suggestion would not work (again, see above posts). Now it turns out that Bill's suggestion needs at least one added step because the fields I need to post to are not in the master table for the post operation. I could add them as dummy fields before the <tbl>.copy() but I do not like the idea of thousands of records having empty, dummy fields in them. Thus before doing the post, I decided to add the following step to add the fields, after which I can post (or append for that matter):

dim fldlist[7] as C
fldlist.initialize(<<%list%
OProd_Code,C,20
ONet_Rate,N,11,4
ONet_Fee,N,11,4
OMargin,N,8,3
OLife_Adjcap,N,8,3
OLock_Dt,D,8,0    
OLock_Exp_Dt,D,8,0
%list%)

a5_add_fields_to_table("mcm_raw",fldlist.dump())

The "O" (for "Original") in front of the field names is what distinguishes them from the values for the last record (e.g., "Oprod_code" and "prod_code").

As confusing as this probably is, I am putting this here with the idea that it might be helpful should anyone run into the same problem, however unlikely that may be! Thanks guys for the help.

Ray Lyons

[Tim Kiebert]

Re: Help creating Temp table

Hi Ray,

Good to here you have had success. I was preparing a response while you posted the above which is now redundent but will include it anyway in case you or anyone else is interested. Your solution sounds cleaner than mine.

_______________________________________________

I did a couple of tests and found the same as you so my idea was not a good one this time. The strange thing is I only used two fields and ended up with identical names. ie no last character substitution as you described. I also could see the data fine.

(as an aside - when I opened the table in restructure mode and then tried to exit restructure it would not let me until I fixed the identical names.)

Here is an other idea. The result of the various copy functions all seem to end up with the same field names as the original table. And you want some of them to be different enough that you can distinguish between the first and last child values. So....

1. Create your temp table with all the fields you want to end up with and called what you want.
2. Create a set with the temp table as parent and your child linked twice.
3. Do an append from the source parent table to the temp table using only the fields you want out of the source parent.
4. Do an update operation on the temp set. Updating the fields in the temp table using the now linked child table(x2)

This assumes that the list of fields your intersted in is always the same

[Raymond Lyons]

Re: Help creating Temp table

Thanks Tim. It sounds like your proposal would work, but as I already have the modification of Bill's suggestion working, I'll probably stick with it. A point in favor of doing it as you suggested is that it would probably avoid all the manual typing (compared with copying and using a genie) that <tbl>.copy() requires (too easy for typing errors to bite me, which they did).

As an aside, I have to wonder what, besides browsing, one could do with a table with duplicate field names in it. For example, could you create a usable form or a report, run a query, etc.? I may check this out just out of curiosity. Not that I would ever want such a table!

Ray