Merging multiple records into one record (HELP)

[Nestor]

Below See4 records out of 100 that i have. Here is what i want to do.

I want to merge or put together record 1, and 2 because for one they have the same P.O. and two they have the same item number except for the last 4 characters (1b05). I want to be able to see one line with the P.O#, Print Date, Item Number (just thefirst 6 characters), and the Total Qty of record 1 and 2 combine. How the heck do I do that??. At this point all i have is one table with these 100 records. can anybody help. Thank you so much..

P.O# PrintDate ItemNumber qty

1 8071 6-7-05 ANM046-1B05 1466

2 8071 6-7-05 ANM046-2B05 207

3 8072 6-8-05 ANM050-1B05 1466

4 8072 6-8-05 ANM050-2B05 207

[-Queue-]

Is this just for printing or do you want to see the same result in Browse Mode?

[Nestor]

I would like to see the result in Browse Mode and ultimately print it. Do you think its duable. Thanks in advance

[clarkmueller]

Any ideas about how to do this? I am looking to do the same thing.

[-Queue-]

I do not think there is any easy way to accomplish this in Browse Mode. In Preview Mode, it's fairly easy.

Create a calculation of Left( ItemNumber; 7 ). Create a subsummary part by this calculation. Remove the body part. Create a summary field that is a total of the qty field and add this to the subsummary part. Sort by P.O., date, and the calculation and enter Preview Mode.

[BobWeaver]

Why not use a selfjoin, or the ValulistItems technique? Or am I missing something?

[-Queue-]

A self-join will still show the number of related records, not a combination of the related ones only. And I do not see how ValueListItems would be very useful here.

[SlimJim]

I think a self-join is a way forward. Can I suggest the following untried solution (I can make no guarantee of success!)

Create a self join using all three fields P.O#. Print Date and Item Number. Now define a new field in the main table as Sum(relatedtable::qty) This should provide the total quantities required and will appear in each record. Now we need to store this value separately from Qty and Sum because it is going to change as we delete records. One way would be to create a new field to store it, just copy the data across before deleting any records.

Now delete the duplicate records. How? I am short of technique here. I would sort them using the three fields, set up three temporary globals and just loop through checking the values and deleting when they match and updating the globals when they don't. There are almost certainly better ways. Once this is done copy the Quantities back from the temporary stores.

[LaRetta]

You might use a temp 'Reports' table with fields: PartPO#, Item#, Date and Quantity (number).

Create PartPO field in main (calculation, text) with: Left(PO; 6). In your graph, join Main::PartPO to Temp:PartPO# on =, join Main::ItemNumber to Temp::ItemNumber on =. Allow Creation to the Temp. Then isolate your found set (using GTRR instead of Find if possible) and:

View As [ Form ]

Go To Field [ any field on form from Main ]

Freeze Window

Go To Record/Request/Page [ First ]

Loop

Set Field [ Temp::Quantity; Temp::Quantity + Main::Quantity ]

Set Field [ Temp::Date; Main::PrintDate ]

Go To Record/Request [ Exit after Last; Next ]

End Loop

Go to Layout [ a temp List layout]

This will create one each unique PO/Item and total the quantity (creating a record as needed). Delete All Records in Temp when you are finished viewing in Browse if you wish; or leave them - they'll never overwrite. I'm unclear if the PrintDate can be different. It will just use one date for each matching set (last one matching PO & Item) which, by your example would be fine. If each line needs to be unique if PrintDate different, just include it in your join (Main::PrintDate = Temp::Date).

LaRetta

[Nestor]

Thank you very much for all the input. Im going to give it a shot.

[Søren Dyhr]

Since the demand is a denormalized, why not go the whole nine yards???

Checkout the upload, I would say that without scripting couldn't it be done ...the challenge is then to hide it in the other dealings ...and here comes:

Now delete the duplicate records. How? I am short of technique here. I would sort them using the three fields, set up three temporary globals and just loop through checking the values and deleting when they match and updating the globals when they don't.

...as a fresh approach, although I wouldn't delete but in stead omit, while gathering the ID of first occurrences.

I'll be back soon with yet another template.

--sd

InvoiceNN.zip

[Nestor]

Laretta can you provide a Demo if you have one. Thank you

[LaRetta]

Okay, down and dirty demo attached.

Also, I noticed I suggested the calc be on part PO. You want Part Item Number - my error (was in a bit of a hurry). I've corrected the script and fields in the attachment. Everything is the same as I've described except the calculation.

Invoices.zip

[comment]

Why not keep it simple?

findDistinct.fp7.zip

[Nestor]

Thank you so much Laretta. That worked perfectly.... Your an angel

[Søren Dyhr]

Why not keep it simple?

Indeed! ...but what about the threads header??? Another issue is the search in an unstored field!!

--sd

[comment]

It depends on how you define "merge" - I happen to think that summing the quantities and showing only a single occurance qualifies. As usual, we don't know the purpose here, so the point may be debatable.

With only 100 records, I don't see searching an unstored field as an issue, but yes, it should be pointed out.

It should be also pointed out that searching an unstored field is not such a big deal in version 7 as it used to be. Fast Summaries could also be used here to good effect. Seems to me we have covered all this here.

[LaRetta]

That worked perfectly.... Your an angel

Nope. In fact, my script is not the best method, Nestor and personally I'd use either of the other methods presented. And my tests are proving it. It was in Finding & Searching Forum so I immediately dismissed summaries and calcs (knowing it's dawg-speed in my huge LineItems). I was wrong to do so.

Thanks, guys. I realized I refer to my 450,000 LineItems file as 'the beast' precisely because I DO heavy-hand it instead of taking full advantage of techniques such as yours. You'll help me tame the beast yet.

LaRetta

[Søren Dyhr]

It depends on how you define "merge" - I happen to think that summing the quantities and showing only a single occurance qualifies.

The thread says "into one record" ...Well according to Queue is a caveat emptor'ish solution much more suited, to the public that have learned from childhood to take all with a grain of salt and reading all texts at hand as Straussian (Leo Strauss) texts ...so your solution might be a tiny hair to clear and obvious ...by giving the "customer" what he actually needs, but not exactly what he asked for!

So unless you give much more than bargained for....

--sd

[comment]

As clear and as obvious as I can make them...

But seriously: I am not selling anything here, so there is no emptor. I am merely presenting another option. And my suggestion is not shrink-wrapped, so the poster can fully weigh the pros and cons.

If you start with the assumption that the poster is not stupid, then he/she is in the best position to judge which option fits best the requirements. This is not, and cannot be, my responsibility. We are only advising here.

[Søren Dyhr]

Oh what a misleading typo I made - What I ment was "TOO" ...we have to "Byzantine" things slightly!!!

I am not selling anything here

Neither am I!!! But you have to consider this:

LEST FOOLS SHOULD FAIL

True wisdom knows

it must comprise

some nonsense

as a compromise,

lest fools shouls fail

to find it wise.

--sd

[comment]

I understood what you meant - I'm saying there's no such thing as too clear and obvious...

"...

lest fools should fail

to find it wise."

And why should I care for the opinions of fools?

More importantly, why do you assume there are fools here?

[Søren Dyhr]

More importantly, why do you assume there are fools here?

I think my endevours are considered as such??

I just wonder how much work can actually be accomplished when one is regularly following tangential pathways whose ultimate dead ends offer no assistance to resolving the problem at hand.

--sd

[comment]

I did not write that, so it's not my place to respond. And in any case it is irrelevant to the subject at hand. If you'd asked me, I would say you are reading too much into it.

[Søren Dyhr]

And in any case it is irrelevant to the subject at hand. If you'd asked me, I would say you are reading too much into it.

Is it??? Am I??? We're talking about design, and the foundation designs are made on. Steve Jobs once said this when interviewed by Wired Mag.:

Design is a funny word. Some people think design means how it looks. But of course, if you dig deeper, it's really how it works. The design of the Mac wasn't what it looked like, although that was part of it. Primarily, it was how it worked. To design something really well, you have to get it. You have to really grok what it's all about. It takes a passionate commitment to really thoroughly understand something, chew it up, not just quickly swallow it. Most people don't take the time to do that.

Creativity is just connecting things. When you ask creative people how they did something, they feel a little guilty because they didn't really do it, they just saw something. It seemed obvious to them after a while. That's because they were able to connect experiences they've had and synthesize new things. And the reason they were able to do that was that they've had more experiences or they have thought more about their experiences than other people.

Unfortunately, that's too rare a commodity. A lot of people in our industry haven't had very diverse experiences. So they don't have enough dots to connect, and they end up with very linear solutions without a broad perspective on the problem. The broader one's understanding of the human experience, the better design we will have.

...snipped from: http://www.wired.com/wired/archive/4.02/jobs_pr.html

So what I here mean, what is it worth if we just post each our own suggestion which surfacially might work or not work precisely as blackboxes ...what if the user due to stress just pick the solution

the way Albert Harum-Alverez once descriped as "for the one holding a hammer everything looks like a nail"

I think a lot is accomplished if we turn from the notion, that we're merely solution providers, into the taoist expression "The journey is the reward" ...where we should be considered as good traveling companions, covering aspects that each member of this tiny community, couldn't think up him/her-self... but all gain from mutually.

--sd

[comment]

what if the user due to stress just pick the solution

Really, S

[SlimJim]

To comment & S

[Nestor]

I have another question regarding something similar. I have the following field names.

ItemNumber Period1 Period2 Period3 Period4

1 CBM001-1B05 25 40 12 120

2 CBM001-2B05 20 10 40 12

3 CBM001-1T05 56 14 46 145

4 CBM001-2T05 23 15 78 34

5 MBM010-1B05 12 67 356 366

6 MBM010-2B05 14 266 256 134

I have about 3000 records similar like this one. Of course with different item numbers. What Im trying to do is the following: i want to add the qty for matching item numbers. The thing is that some item numbers have the same first 6 numbers, what difference them from all others is the last 4 #'s. For example i would want record 1 and 2 to be add together and turn them into one record. the same thing with record 3, and 4, and 5 and 6. Qty's for cbm001-1b05 correspond with cbm001-2b05. the key is the 1b05, or 1t05. if item numbers have the same first 6 numbers the same and have the following numbers like 1b05 and 2b05 i would like to group them together and make one record with qty's for each period add it up. So ANY IDEAS HOW THIS CAN BE DONE??? thank you very much