count total childrens and identifying duplicates

[Ugo DI LUCA]

I can't get these result shown and this is very very very irritating.

I wan't to see for a set of records in my Line Item (children) attached to one customer order (parent) the number of different vendors.

For the moment, using self realtionships, all I reached to do is :

- Having the total number of lines for Customer Order # X - not useful !

- Having the number of duplicates (shown for each lines) - i.e. Line N

[djgogi]

The first part is pretty straightforward:

After defining an self relationship "CurrentOrder" in LineItems file OrderID--OrderIDdefine an value list "Vendors" as use only related values from field Vendor (you'll need to define this field as look up from field vendors in file Products using relationship productID(from line items file) to productID (from Product file))form relationship "CurrentOrder".

Always in same file create an calculated field numOfVendors=PatternCount(ValueListItems(Status(CurrentFileName), "CurrentOrder"),"

[Ugo DI LUCA]

Grazie Mille DJ !!

Very clear post. It gives me another idea and leads to a third question.

My product file holds the inventory.

The inventory is calculated through self relationships into the line items.

I posted the test file recently if you want to see it.

The specificity of my business is that any single product has a code attached to it that is different according to shade/calibre. To make it clear, think of a product with a validity date. It doesn't change the product ID, nor the price or other caracteristics, but you have to deal with multiple inventory for the same product (multiple validity date).

So...

I created a value list that relate to product_ID&Shade/Calibre. Using one value will give me the different lines attached to it, the residual stock,...It works perfectly. But I need to use the value list.

For a better viewing, would it be possible to have the portal in Product File, a record that is the result of a selfjoin in the Line Items file...filtering the portal to see detailed records attached to this shade/calibre ?

Thank you again for your quick and clear answer.

[Ugo DI LUCA]

OK DJ. Done some work with your post and have made some adjustments. Seem to me their were some errors but just tell me if I'm wrong. Anyway, I've just learned even more from errors as I could catch the different results, and gave me other ideas.

Always in same file create an calculated field numOfVendors=PatternCount(ValueListItems(Status(CurrentFileName), "CurrentOrder"),"

[BobWeaver]

'^' is the exponentiation operator, so 10^n is ten to the power of n. More simply, it's just one with n zeroes after it.

[Ugo DI LUCA]

I've still got an error message in setting this definition. I already encounter problem while setting definitions, using some of the post from this forum. For example, most definitions with ; are rebuilt with , in the french version.

[BobWeaver]

For a leading zeroes formula, I usually use:

Right ("0000000000"&Number,n)

where n is the actual number of digits that you want. Just make sure that the prefix string contains at least that many zeroes.

[Ugo DI LUCA]

Do you mean DJ's first post was just an abbreviation of these ten 0's. Or is the 10^n a formula. Just to know if I was a pest or an idiot

[BobWeaver]

Actually, I was wondering about that too. So, I had to look it up, and yes '^' is an actual operator. Obviously I don't use it much.

The French version may use a different symbol. Look up the arithmetic operators in Chapter 11.

[djgogi]

numOfVendors=PatternCount(ValueListItems(Status(CurrentFileName), "CurrentOrder"),"

[djgogi]

It is just an dynamic way to achieve the same thing.

There are some handy ways for using it like:

Substitute(Right(10^n,n), "0", "|")

Make 2(or 3) copies of the field with above definition on the layout, (make them transparent) first make them overlapping than move one of them 1 px to the left and the other one (in case of 3 copies) on px to the right, and voil

[djgogi]

Just in case you might need more than 214 repetitions:

Left(Substitute(Right(10^n,n), "0", Substitute(Right(10^n,n), "0", anyString)) ,64000)

Dj

[Ugo DI LUCA]

It is just an dynamic way to achieve the same thing.

There are some handy ways for using it like:

Substitute(Right(10^n,n), "0", "|")

Make 2(or 3) copies of the field with above definition on the layout, (make them transparent) first make them overlapping than move one of them 1 px to the left and the other one (in case of 3 copies) on px to the right, and voil

[djgogi]

Sorry, I was replying (and extending the post) to Bob's remarque.

Dj

[Ugo DI LUCA]

OK DJ, I found this is the unique default of the new forum.

Do you have an answer for the third question I asked on this post. I've reposted it with more detail in the Portal section. Havingfields from LineItem seljoin to show as a list in a portal into Product File...