Hi

I have the following two tables, which I would like to merge into one table.

Table 1: Table 2:
Invoice header Invoice lines

Containing: Containing:
Invoice number Invoice number
Name Item
Street Item serialnumber
City Quantity

The new table should contain one record per Invoice number containing:

Invoice number
Name
Street
City
Item [20]
Item serialnumber [20]
Quantiy [20]

Can anybody make a script or other solution that can make the merge?

Thanks

Daniel

In article <42cce32c$0$153$edfadb0f (AT) dread11 (DOT) news.tele.dk>, "D"
<d (AT) msn (DOT) com> wrote:

If "Quantity [20]" means a field with 20 repetitions, then the answer
to your question is "JUST SAY NO!!"

Repeating fields are a "pain in the behind" and you're much better off
(95% of the time) with the 'two tables and a relationship' method that
you've obviously already got, especially if "Item SerialNumber" is
being used to lookup a unit price from another table.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hi Harry

Do you have a solution to adding the fields to one table in another way.

E.g. adding each line to a field - item1, item2, item3...

Daniel

D wrote:
And what's up with table 2?

I am making a quiry that will work much faster, when this merge has been
done. Due to the fact that I also compare with external (ODBC) datasources
in this quiry.

Table 2 contains the individual lines of each invoice, where table 1
contains the header of the invoice.

Can you help me?

Daniel

In article <42cd0b7f$0$7804$edfadb0f (AT) dread11 (DOT) news.tele.dk>, "D"
<d (AT) msn (DOT) com> wrote:

You probably shouldn't.

Repeating fields are painful to use in many situations, so the invoice
system is probably better off using the related files as it is, unless
you have some VERY specific reason for combining it all.

Separate Line Item fields may work, but depends on what else you may
want to do with the data.

But if you still really want to do this, the only way to combine the
related records into each parent record would be to have a script that
loops through each record and within that copies the related data
across to the new fields (whether they're separate line fields or one
field with repetitions).
ie.
Go To Record / Request / Page [First]
Loop
Go To Portal Row [By Number, 1]
Set Field [Item1, RelatedFile::Item]
Set Field [Quantity1, RelatedFile::Quantity]
...

Go To Portal Row [By Number, 2]
Set Field [Item2, RelatedFile::Item]
Set Field [Quantity2, RelatedFile::Quantity]
...

Go To Portal Row [By Number, X]
Set Field [ItemX, RelatedFile::Item]
Set Field [QuantityX, RelatedFile::Quantity]

Go To Record / Request / Page [Next, Exit After Last]
End Loop



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

You are a "helpful" genius Helpful Harry!!! :-)

I implemented your code like this, is that the right way?
(The portal consists of 20 rows and 2 columns)

Go to Record/Request/Page [First]
Loop
Go to Portal Row [No dialog;1]
Set Field [Fakturaer::Varenr1; Linie::Varenr]
Set Field [Fakturaer::Antal1; Linier::Antal]
Go to Portal Row [No dialog;2]
Set Field [Fakturaer::Varenr2; Linie::Varenr]
Set Field [Fakturaer::Antal2; Linier::Antal]
Go to Portal Row [No dialog;3]
Set Field [Fakturaer::Varenr3; Linie::Varenr]
Set Field [Fakturaer::Antal3; Linier::Antal]
....
Go to Portal Row [No dialog;20]
Set Field [Fakturaer::Varenr20; Linie::Varenr]
Set Field [Fakturaer::Antal20; Linier::Antal]
Go to Record/Request/Page [Next; Exit af last}
End Loop

I am just faced with a single problem, if the record is empty it keeps
copying the last record into the new fields.

E.g.
Portal New records
Varenr. Antal Varenr. Antal
7000 2 Line1 7000 2
3000 2 Line2 3000 2
2700 3 Line3 2700 3
Line4 2700 3
Line5 2700 3

How can I make it test whether the portals row is empty and if so make it
jump to the next record via "Go To Record / Request / Page [Next, Exit After
Last]?

Thanks

Daniel

In article <42ce6c95$0$185$edfadb0f (AT) dread11 (DOT) news.tele.dk>, "D"
<d (AT) msn (DOT) com> wrote:


Opps! Sorry, I forgot about the possibility of empty rows. To avoid
blank rows you'll need to use an If statement wrapped around each block
of Set Field commands.
ie. (new lines are marked with *)

Go to Record/Request/Page [First]
Loop
Go to Portal Row [No dialog;1]
* If [not(IsEmpty(Linie::LINKFIELD))]
Set Field [Fakturaer::Varenr1; Linie::Varenr]
Set Field [Fakturaer::Antal1; Linier::Antal]
* End If
Go to Portal Row [No dialog;2]
* If [not(IsEmpty(Linie::LINKFIELD))]
Set Field [Fakturaer::Varenr2; Linie::Varenr]
Set Field [Fakturaer::Antal2; Linier::Antal]
* End If
Go to Portal Row [No dialog;3]
* If [not(IsEmpty(Linie::LINKFIELD))]
Set Field [Fakturaer::Varenr3; Linie::Varenr]
Set Field [Fakturaer::Antal3; Linier::Antal]
* End If
...
Go to Portal Row [No dialog;20]
* If [not(IsEmpty(Linie::LINKFIELD))]
Set Field [Fakturaer::Varenr20; Linie::Varenr]
Set Field [Fakturaer::Antal20; Linier::Antal]
* End If
Go to Record/Request/Page [Next; Exit af last}
End Loop

where Linie::LINKFIELD is the field in the related file / table that is
used in the Relationship to link the Parent and Related data - if that
field is empty for the portal row you're on, then the entire portal row
is blank and you can skip the Set Field commands. )

It will still go through all 20 rows looking for data before going on
to the next record, but it's really not worth complicating the script
to avoid that ... unless it's going to be run often and most of the
records only use a few portal rows.

Obviously I have no idea if the field names you've used are correct or
not. You say it works (apart from the blank rows problem), so it must
be right ... I'm guessing the destination fields are actually in
another file / table which is why you're using a Fakturaer relationship
in front of each one.




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)