Dumb question but here goes

want to show only the last time each product was bought by a customer on their form in portal. want to relate to each unique product from lineitems for that customer and only have that last purchase show in a portal. one of each product. possible? how?

i have product table too. tried to use it related to lineitem then put that (product) in portal. got myself quite lost in it. can't relate product because it doesn't have customercode in it. searching here didnt show it was possible without export. that would take forever and wouldn't stay current.

Dumb question but here goes

want to show only the last time each product was bought by a customer on their form in portal. want to relate to each unique product from lineitems for that customer and only have that last purchase show in a portal. one of each product. possible? how?

i have product table too. tried to use it related to lineitem then put that (product) in portal. got myself quite lost in it. can't relate product because it doesn't have customercode in it. searching here didnt show it was possible without export. that would take forever and wouldn't stay current.

Dumb question but here goes

want to show only the last time each product was bought by a customer on their form in portal. want to relate to each unique product from lineitems for that customer and only have that last purchase show in a portal. one of each product. possible? how?

i have product table too. tried to use it related to lineitem then put that (product) in portal. got myself quite lost in it. can't relate product because it doesn't have customercode in it. searching here didnt show it was possible without export. that would take forever and wouldn't stay current.

Try the Last() function.

Try the Last() function.

Try the Last() function.

I can't think of a way to do it without either a script, or possibly a custom function. I couldn't write the custom function, but perhaps someone might be able to.

In a script you would go their line item records, sort by Product ID and date descending. Then use a loop to remove duplicate Product IDs, keeping the 1st.

That would give you the records. Then do a Copy All Records technique, copying only a unique serial ID for each record. Then return to the Customer table and paste into a field.

Use that for a portal related to Line Items, and you've got the latest products for that customer.

It would not "stay current," unless you scripted the above to happen. I imagine it would be a pretty fast script, if you did the Loop right, unless each customer has really a lot of line items.

Alternatively you could script all additions/editing of line items (product choice anyway). That would allow you to "mark" the latest line item record, for that customer-product, by going to the next earlier (or all earlier) and clearing the mark.

It's much more difficult to mark the last than it is the first, because the earlier mark(s) have to be cleared. And a deletion after marking means you must go back and re-mark the previous remaining.

I can't think of a way to do it without either a script, or possibly a custom function. I couldn't write the custom function, but perhaps someone might be able to.

In a script you would go their line item records, sort by Product ID and date descending. Then use a loop to remove duplicate Product IDs, keeping the 1st.

That would give you the records. Then do a Copy All Records technique, copying only a unique serial ID for each record. Then return to the Customer table and paste into a field.

Use that for a portal related to Line Items, and you've got the latest products for that customer.

It would not "stay current," unless you scripted the above to happen. I imagine it would be a pretty fast script, if you did the Loop right, unless each customer has really a lot of line items.

Alternatively you could script all additions/editing of line items (product choice anyway). That would allow you to "mark" the latest line item record, for that customer-product, by going to the next earlier (or all earlier) and clearing the mark.

It's much more difficult to mark the last than it is the first, because the earlier mark(s) have to be cleared. And a deletion after marking means you must go back and re-mark the previous remaining.

I can't think of a way to do it without either a script, or possibly a custom function. I couldn't write the custom function, but perhaps someone might be able to.

In a script you would go their line item records, sort by Product ID and date descending. Then use a loop to remove duplicate Product IDs, keeping the 1st.

That would give you the records. Then do a Copy All Records technique, copying only a unique serial ID for each record. Then return to the Customer table and paste into a field.

Use that for a portal related to Line Items, and you've got the latest products for that customer.

It would not "stay current," unless you scripted the above to happen. I imagine it would be a pretty fast script, if you did the Loop right, unless each customer has really a lot of line items.

Alternatively you could script all additions/editing of line items (product choice anyway). That would allow you to "mark" the latest line item record, for that customer-product, by going to the next earlier (or all earlier) and clearing the mark.

It's much more difficult to mark the last than it is the first, because the earlier mark(s) have to be cleared. And a deletion after marking means you must go back and re-mark the previous remaining.

Perhaps it can be achieved by simpler means - if we disregard the "in portal" requirement, and focus on the purpose instead?

Perhaps it can be achieved by simpler means - if we disregard the "in portal" requirement, and focus on the purpose instead?

Perhaps it can be achieved by simpler means - if we disregard the "in portal" requirement, and focus on the purpose instead?

LastPurchases.fp7.zip

thanks guys. Ok. Purpose

The guys want to know, while talking to thier customer, the last price they bought materials (gives them negotiating room if they weren't the salesman on the last sale - keeps them within guidelines and they know how much they can play with), how much they ordered and when (tells them how much to suggest gets ordered this time, guys know how much time has past and they can predict). and so they can scan our product list too and see what they HAVEN'T bought to suggest new products, etc.

Figured if they had a list of our products, showing price and quantity last paid they could tell ... and see holes where they hadn't bought (on those lines next to our product names). great sales help tool they say. before fm, they had to keep their own lists on paper now they want miracles.

I suppose it could be exported or sort & omit or anything. But that's what they need. Ive been writing to another table using relationship match on CustomerCode & ProductCode and setting a field with add related of purchase date , quantity and price. It overwrites the same customercodeProductCode key fields with the latest date, quantity and price fields.

but they want it instant instead of nighly and sometimes it's too days before i find out something didn't actually sell then i'm spending my nights finding those things and deleting them. it's driving me nuts and they are still unhappy about it. and those sales just keep growing and the file is getting bigger. Would rather handle it with relation than sort and find, I think.

I was hoping 7 had changed some things - like snake-eating-tail recursions and global calculations.

oh. you attached something. didn't see it. thank you. i'll look

Pete

thanks guys. Ok. Purpose

The guys want to know, while talking to thier customer, the last price they bought materials (gives them negotiating room if they weren't the salesman on the last sale - keeps them within guidelines and they know how much they can play with), how much they ordered and when (tells them how much to suggest gets ordered this time, guys know how much time has past and they can predict). and so they can scan our product list too and see what they HAVEN'T bought to suggest new products, etc.

Figured if they had a list of our products, showing price and quantity last paid they could tell ... and see holes where they hadn't bought (on those lines next to our product names). great sales help tool they say. before fm, they had to keep their own lists on paper now they want miracles.

I suppose it could be exported or sort & omit or anything. But that's what they need. Ive been writing to another table using relationship match on CustomerCode & ProductCode and setting a field with add related of purchase date , quantity and price. It overwrites the same customercodeProductCode key fields with the latest date, quantity and price fields.

but they want it instant instead of nighly and sometimes it's too days before i find out something didn't actually sell then i'm spending my nights finding those things and deleting them. it's driving me nuts and they are still unhappy about it. and those sales just keep growing and the file is getting bigger. Would rather handle it with relation than sort and find, I think.

I was hoping 7 had changed some things - like snake-eating-tail recursions and global calculations.

oh. you attached something. didn't see it. thank you. i'll look

Pete

thanks guys. Ok. Purpose

The guys want to know, while talking to thier customer, the last price they bought materials (gives them negotiating room if they weren't the salesman on the last sale - keeps them within guidelines and they know how much they can play with), how much they ordered and when (tells them how much to suggest gets ordered this time, guys know how much time has past and they can predict). and so they can scan our product list too and see what they HAVEN'T bought to suggest new products, etc.

Figured if they had a list of our products, showing price and quantity last paid they could tell ... and see holes where they hadn't bought (on those lines next to our product names). great sales help tool they say. before fm, they had to keep their own lists on paper now they want miracles.

I suppose it could be exported or sort & omit or anything. But that's what they need. Ive been writing to another table using relationship match on CustomerCode & ProductCode and setting a field with add related of purchase date , quantity and price. It overwrites the same customercodeProductCode key fields with the latest date, quantity and price fields.

but they want it instant instead of nighly and sometimes it's too days before i find out something didn't actually sell then i'm spending my nights finding those things and deleting them. it's driving me nuts and they are still unhappy about it. and those sales just keep growing and the file is getting bigger. Would rather handle it with relation than sort and find, I think.

I was hoping 7 had changed some things - like snake-eating-tail recursions and global calculations.

oh. you attached something. didn't see it. thank you. i'll look

Pete

Eureka. I confess to be guilty of old-fashioned "6-ish" methods. Comment has the idea. The data has to come from the Products table, as it's the only place the product would only appear once. But the date has to show from Line Items.

Rather than setting the CustomerID into a global in Products, we could filter products for a customer by using another table occurrence of the line items table in between.

This allows each product to appear only once, in a portal, on a Customer table layout. The date comes from another line items TO, with the relationship sorted descending by date.

It's the same, but with only one line per product, in a portal. It still needs the global in Products to be set to the CustomerID. And the portal will be sorted by product, if at all, because the date is not in the product table.

Eureka. I confess to be guilty of old-fashioned "6-ish" methods. Comment has the idea. The data has to come from the Products table, as it's the only place the product would only appear once. But the date has to show from Line Items.

Rather than setting the CustomerID into a global in Products, we could filter products for a customer by using another table occurrence of the line items table in between.

This allows each product to appear only once, in a portal, on a Customer table layout. The date comes from another line items TO, with the relationship sorted descending by date.

It's the same, but with only one line per product, in a portal. It still needs the global in Products to be set to the CustomerID. And the portal will be sorted by product, if at all, because the date is not in the product table.

Eureka. I confess to be guilty of old-fashioned "6-ish" methods. Comment has the idea. The data has to come from the Products table, as it's the only place the product would only appear once. But the date has to show from Line Items.

Rather than setting the CustomerID into a global in Products, we could filter products for a customer by using another table occurrence of the line items table in between.

This allows each product to appear only once, in a portal, on a Customer table layout. The date comes from another line items TO, with the relationship sorted descending by date.

It's the same, but with only one line per product, in a portal. It still needs the global in Products to be set to the CustomerID. And the portal will be sorted by product, if at all, because the date is not in the product table.

LastPurchases2.zip

wow. one's a form and one's a report. but that graph is confusing and you don't even use a GTRR or find. Please explain your thinking. how did you make this work? Only a body in report not even sub-part or summary or sort or anything.

i've tried this and even GTRR couldn't relate to last one. not relating on date or last serial. no custom function and no calculation, no set field calc in script (exept ID, wow). I'm speachless. Please explain how it works! this is a miracle

wow. one's a form and one's a report. but that graph is confusing and you don't even use a GTRR or find. Please explain your thinking. how did you make this work? Only a body in report not even sub-part or summary or sort or anything.

i've tried this and even GTRR couldn't relate to last one. not relating on date or last serial. no custom function and no calculation, no set field calc in script (exept ID, wow). I'm speachless. Please explain how it works! this is a miracle

wow. one's a form and one's a report. but that graph is confusing and you don't even use a GTRR or find. Please explain your thinking. how did you make this work? Only a body in report not even sub-part or summary or sort or anything.

i've tried this and even GTRR couldn't relate to last one. not relating on date or last serial. no custom function and no calculation, no set field calc in script (exept ID, wow). I'm speachless. Please explain how it works! this is a miracle

double wow. i've played with these relationships for a week. similar (sliding products in between customers and lineitems - duplicating lineitems and attaching this way and that, inserting globals everywhere, passing global calcs and ids like crazy - but didn't understand how to pull it off - still don't. Fenton yours shouldn't work either.

If i can understand this concept i can rule the world. Well almost. 7 IS DIFFERENT. this changes everything. everything. Fess up guys. why does this work? Can you walk me through it. Help me understand HOW it works please? I feel like I've just had it proven that gravity falls up.

Oh. first message was responding to Comment and second to Fenton. They are so fast together!

Pete

double wow. i've played with these relationships for a week. similar (sliding products in between customers and lineitems - duplicating lineitems and attaching this way and that, inserting globals everywhere, passing global calcs and ids like crazy - but didn't understand how to pull it off - still don't. Fenton yours shouldn't work either.

If i can understand this concept i can rule the world. Well almost. 7 IS DIFFERENT. this changes everything. everything. Fess up guys. why does this work? Can you walk me through it. Help me understand HOW it works please? I feel like I've just had it proven that gravity falls up.

Oh. first message was responding to Comment and second to Fenton. They are so fast together!

Pete

double wow. i've played with these relationships for a week. similar (sliding products in between customers and lineitems - duplicating lineitems and attaching this way and that, inserting globals everywhere, passing global calcs and ids like crazy - but didn't understand how to pull it off - still don't. Fenton yours shouldn't work either.

If i can understand this concept i can rule the world. Well almost. 7 IS DIFFERENT. this changes everything. everything. Fess up guys. why does this work? Can you walk me through it. Help me understand HOW it works please? I feel like I've just had it proven that gravity falls up.

Oh. first message was responding to Comment and second to Fenton. They are so fast together!

Pete

Eureka is right! I see how the customerID is passed to products. I couldn't pass it on through. you just used another TO of it! then the SECOND TO of products (and Fenton slid another Lineitems in there) uses that CustomerID. You passed it that way - through - by another TO. That's amazing. yes!

sorry . a bit excited.

ok. i'll shut up and listen now.

Eureka is right! I see how the customerID is passed to products. I couldn't pass it on through. you just used another TO of it! then the SECOND TO of products (and Fenton slid another Lineitems in there) uses that CustomerID. You passed it that way - through - by another TO. That's amazing. yes!

sorry . a bit excited.

ok. i'll shut up and listen now.

Eureka is right! I see how the customerID is passed to products. I couldn't pass it on through. you just used another TO of it! then the SECOND TO of products (and Fenton slid another Lineitems in there) uses that CustomerID. You passed it that way - through - by another TO. That's amazing. yes!

sorry . a bit excited.

ok. i'll shut up and listen now.

The top row of the graph is your standard Customers - LineItems - Product set up.

The bottom row is the "viewer": you start in the Products table, in list view, so you always see ALL products - regardless of whether the selected customer bought them or not.

The relationship to LineItems2 shows only sales of this product to this customer (i.e. gCustomer). The relationship is sorted by Date, descending. Therefore the FIRST related record is the LAST purchase of this product by this customer. When you put fields from LineItems2 directly on the Products layout (NOT in a portal), they always show data from the FIRST related record.

The top row of the graph is your standard Customers - LineItems - Product set up.

The bottom row is the "viewer": you start in the Products table, in list view, so you always see ALL products - regardless of whether the selected customer bought them or not.

The relationship to LineItems2 shows only sales of this product to this customer (i.e. gCustomer). The relationship is sorted by Date, descending. Therefore the FIRST related record is the LAST purchase of this product by this customer. When you put fields from LineItems2 directly on the Products layout (NOT in a portal), they always show data from the FIRST related record.

The top row of the graph is your standard Customers - LineItems - Product set up.

The bottom row is the "viewer": you start in the Products table, in list view, so you always see ALL products - regardless of whether the selected customer bought them or not.

The relationship to LineItems2 shows only sales of this product to this customer (i.e. gCustomer). The relationship is sorted by Date, descending. Therefore the FIRST related record is the LAST purchase of this product by this customer. When you put fields from LineItems2 directly on the Products layout (NOT in a portal), they always show data from the FIRST related record.

I wish I knew how to do that. That is I can (barely) understand how it works when I see it, but I cannot come up with it. It's like watching someone solve a math problem - you can follow the explanation, but how the heck did he know which way to go?

A question: I see that the 2 branches of the graph are similar, except for the ending. Is there any reason not to roll them into one (see attached pic)?

I wish I knew how to do that. That is I can (barely) understand how it works when I see it, but I cannot come up with it. It's like watching someone solve a math problem - you can follow the explanation, but how the heck did he know which way to go?

A question: I see that the 2 branches of the graph are similar, except for the ending. Is there any reason not to roll them into one (see attached pic)?

I wish I knew how to do that. That is I can (barely) understand how it works when I see it, but I cannot come up with it. It's like watching someone solve a math problem - you can follow the explanation, but how the heck did he know which way to go?

A question: I see that the 2 branches of the graph are similar, except for the ending. Is there any reason not to roll them into one (see attached pic)?

Well, it does NOT use the relationships to pass the CustomerID; it still uses has to set the CustomerID global in Products, as Comment set up. The Line Items 2 TO I slipped in could not be used to filter by Customer "through" the Products; because there is no customer in Products. It's purpose was only to "filter" the products to only those for the customer.

Otherwise you lose the customer looking further, into the last Line Items table; because there is no customer field in Products.

The portal is in the Customer file, but you still have to set the customer ID into that related global. The global is then used to look further, into the Line Items 3 TO, to get the "last date" for that product & global customer.

It is the "last" date because that relationship (not the portal's, just the date's) is sorted descending by date. So, the date is not of the same relationship as the portal, but is looking one further down the line; much like putting a related field on a layout.

Because the relationship for that field is logically valid, the date makes sense. The portal is looking at each product belonging to a Customer (because its target is the Products table, filtered through the Line Items 2 TO by CustomerID).

The global relationship for the date is looking on to the Line Items 3 TO, based on that customer's ID (set into a global field) AND the product ID. So it's continuing the same logical connection.

Well, it does NOT use the relationships to pass the CustomerID; it still uses has to set the CustomerID global in Products, as Comment set up. The Line Items 2 TO I slipped in could not be used to filter by Customer "through" the Products; because there is no customer in Products. It's purpose was only to "filter" the products to only those for the customer.

Otherwise you lose the customer looking further, into the last Line Items table; because there is no customer field in Products.

The portal is in the Customer file, but you still have to set the customer ID into that related global. The global is then used to look further, into the Line Items 3 TO, to get the "last date" for that product & global customer.

It is the "last" date because that relationship (not the portal's, just the date's) is sorted descending by date. So, the date is not of the same relationship as the portal, but is looking one further down the line; much like putting a related field on a layout.

Because the relationship for that field is logically valid, the date makes sense. The portal is looking at each product belonging to a Customer (because its target is the Products table, filtered through the Line Items 2 TO by CustomerID).

The global relationship for the date is looking on to the Line Items 3 TO, based on that customer's ID (set into a global field) AND the product ID. So it's continuing the same logical connection.

Well, it does NOT use the relationships to pass the CustomerID; it still uses has to set the CustomerID global in Products, as Comment set up. The Line Items 2 TO I slipped in could not be used to filter by Customer "through" the Products; because there is no customer in Products. It's purpose was only to "filter" the products to only those for the customer.

Otherwise you lose the customer looking further, into the last Line Items table; because there is no customer field in Products.

The portal is in the Customer file, but you still have to set the customer ID into that related global. The global is then used to look further, into the Line Items 3 TO, to get the "last date" for that product & global customer.

It is the "last" date because that relationship (not the portal's, just the date's) is sorted descending by date. So, the date is not of the same relationship as the portal, but is looking one further down the line; much like putting a related field on a layout.

Because the relationship for that field is logically valid, the date makes sense. The portal is looking at each product belonging to a Customer (because its target is the Products table, filtered through the Line Items 2 TO by CustomerID).

The global relationship for the date is looking on to the Line Items 3 TO, based on that customer's ID (set into a global field) AND the product ID. So it's continuing the same logical connection.

I didn't know how to do it at first, I wasn't thinking in 7 terms. Your setting the CustomerID global in Products solved much of the problem. But I thought "why not use the Line Items to filter the products to only the customer line items?" This is similar logically to using ValueListItems of a filtered relationship. So we solved it step by step, which I'm pretty good at. I wouldn't have come up with the whole thing on my own, as I would have been stuck on not being able to get the customer to go clear through.

Yes, it could be combined into one line relational line. No real reason not to.

I didn't know how to do it at first, I wasn't thinking in 7 terms. Your setting the CustomerID global in Products solved much of the problem. But I thought "why not use the Line Items to filter the products to only the customer line items?" This is similar logically to using ValueListItems of a filtered relationship. So we solved it step by step, which I'm pretty good at. I wouldn't have come up with the whole thing on my own, as I would have been stuck on not being able to get the customer to go clear through.

Yes, it could be combined into one line relational line. No real reason not to.

I didn't know how to do it at first, I wasn't thinking in 7 terms. Your setting the CustomerID global in Products solved much of the problem. But I thought "why not use the Line Items to filter the products to only the customer line items?" This is similar logically to using ValueListItems of a filtered relationship. So we solved it step by step, which I'm pretty good at. I wouldn't have come up with the whole thing on my own, as I would have been stuck on not being able to get the customer to go clear through.

Yes, it could be combined into one line relational line. No real reason not to.

LastPurchases2.zip

Thanks.

The funny thing about your remark "wasn't thinking in 7 terms"? If you concatenate CustomerID & Product ID in LineItems, and gCustomerID & Product ID in Products, my idea will work in any version from 3 up. So it's debatable who was thinking in what terms...

Thanks.

The funny thing about your remark "wasn't thinking in 7 terms"? If you concatenate CustomerID & Product ID in LineItems, and gCustomerID & Product ID in Products, my idea will work in any version from 3 up. So it's debatable who was thinking in what terms...

Thanks.

The funny thing about your remark "wasn't thinking in 7 terms"? If you concatenate CustomerID & Product ID in LineItems, and gCustomerID & Product ID in Products, my idea will work in any version from 3 up. So it's debatable who was thinking in what terms...

Hi,

Yes 7 solved this old problem of displaying only one line of the same. The only problem then is having any count running on the item "group" displayed in the Product Portal...

I personnally use an unstored calculation in the LineItems and both displays would happen in the LineItems itself.

Attached an example.

Hi,

Yes 7 solved this old problem of displaying only one line of the same. The only problem then is having any count running on the item "group" displayed in the Product Portal...

I personnally use an unstored calculation in the LineItems and both displays would happen in the LineItems itself.

Attached an example.

Hi,

Yes 7 solved this old problem of displaying only one line of the same. The only problem then is having any count running on the item "group" displayed in the Product Portal...

I personnally use an unstored calculation in the LineItems and both displays would happen in the LineItems itself.

Attached an example.

LastPurchases_altern.fp7.zip

Fenton thank you, your explanation makes sense but the concept is elusive to me (but it'll sink in if I stick with it and I will). Comment, you say this can be done in prior version using concentrated keys? I read (I swear) 200 posts here they all said it couldn't (prior versions) without writing lines with script or export! I swear it!! Ugo, you now say, "With 7, it's possible now."

Thank you all for demos. I will keep this thread and each demo as a demonstration of the progression of thinking. Quite valuable to me - more than you'll know. For prior versions, is this like noone believing the 3-minute mile was physically possible until someone did it? Then within one year (after it had been proved possible), several people did it? It had to be proven first?

I can't even go to sleep on this one and I keep trying things. It should then be possible to adjust it so that all products show, so the 'holes' (unpurchased puroducts) also show but I guess I haven't grasped it enough to adjust it because I can't make it do it. Products must slide before LineItems, right? Wow, to have both methods at my control ... and I assume additional filters can be added easily also by passing additional globals? I know our guys ... they'll want to filter by other things like Customer Type or Territory. Either of these two ideas possible in this situation (all products showing holes and/or filtering further)?

Hey! isn't this an example of why/when globals are wonderful? That thread about 'why I use globals?'

Ugo, I thought unstored was bad and yours uses two unstored and theyll be in a big file. Can you explain which would be best then yours or Fenton's (since both give me Unique listing)? Not sure which to use. Fenton's looks clearer but I don't understand either enough yet.

Forums is best thing existing for help ever. Can't tell you how much I appreciate you all.

Fenton thank you, your explanation makes sense but the concept is elusive to me (but it'll sink in if I stick with it and I will). Comment, you say this can be done in prior version using concentrated keys? I read (I swear) 200 posts here they all said it couldn't (prior versions) without writing lines with script or export! I swear it!! Ugo, you now say, "With 7, it's possible now."

Thank you all for demos. I will keep this thread and each demo as a demonstration of the progression of thinking. Quite valuable to me - more than you'll know. For prior versions, is this like noone believing the 3-minute mile was physically possible until someone did it? Then within one year (after it had been proved possible), several people did it? It had to be proven first?

I can't even go to sleep on this one and I keep trying things. It should then be possible to adjust it so that all products show, so the 'holes' (unpurchased puroducts) also show but I guess I haven't grasped it enough to adjust it because I can't make it do it. Products must slide before LineItems, right? Wow, to have both methods at my control ... and I assume additional filters can be added easily also by passing additional globals? I know our guys ... they'll want to filter by other things like Customer Type or Territory. Either of these two ideas possible in this situation (all products showing holes and/or filtering further)?

Hey! isn't this an example of why/when globals are wonderful? That thread about 'why I use globals?'

Ugo, I thought unstored was bad and yours uses two unstored and theyll be in a big file. Can you explain which would be best then yours or Fenton's (since both give me Unique listing)? Not sure which to use. Fenton's looks clearer but I don't understand either enough yet.

Forums is best thing existing for help ever. Can't tell you how much I appreciate you all.

Fenton thank you, your explanation makes sense but the concept is elusive to me (but it'll sink in if I stick with it and I will). Comment, you say this can be done in prior version using concentrated keys? I read (I swear) 200 posts here they all said it couldn't (prior versions) without writing lines with script or export! I swear it!! Ugo, you now say, "With 7, it's possible now."

Thank you all for demos. I will keep this thread and each demo as a demonstration of the progression of thinking. Quite valuable to me - more than you'll know. For prior versions, is this like noone believing the 3-minute mile was physically possible until someone did it? Then within one year (after it had been proved possible), several people did it? It had to be proven first?

I can't even go to sleep on this one and I keep trying things. It should then be possible to adjust it so that all products show, so the 'holes' (unpurchased puroducts) also show but I guess I haven't grasped it enough to adjust it because I can't make it do it. Products must slide before LineItems, right? Wow, to have both methods at my control ... and I assume additional filters can be added easily also by passing additional globals? I know our guys ... they'll want to filter by other things like Customer Type or Territory. Either of these two ideas possible in this situation (all products showing holes and/or filtering further)?

Hey! isn't this an example of why/when globals are wonderful? That thread about 'why I use globals?'

Ugo, I thought unstored was bad and yours uses two unstored and theyll be in a big file. Can you explain which would be best then yours or Fenton's (since both give me Unique listing)? Not sure which to use. Fenton's looks clearer but I don't understand either enough yet.

Forums is best thing existing for help ever. Can't tell you how much I appreciate you all.

Ugo's is better. Because he used a calculation in Line Items itself you don't have to set a global. A script is therefore no longer needed. You could put his portal directly on the main Customer layout and it would still work, even when flipping to the next record using the status area. This eliminates a navigation problem.

As far as the unstored calculation causing slowness, it might appear that my method was more direct. But there is another factor. I've read that because of the way Server 7 distributes the workload, using a global field in a calculation is going to be slower; something about having to pass the local global to Server for it to evaluate the calculation, whereas it doesn't have to do this for Ugo's calculation. Probably a small difference, but it tips the scale. I've thought of this also in regard to calendar and scheduling solutions, where a global is in many calculations and relationships, all on the same layout.

While I've used a self-relationship to mark either the first or the last record before pre-7, it was never like this; looking through it at another instance of the same table, to produce unique lines in a portal from Customers.

So, while going through an extra TO to filter records may have other uses, I was solving a problem that didn't need to exist (again).

BTW, you will want another plain Product TO off of plain Line Items, for adding products.

Ugo's is better. Because he used a calculation in Line Items itself you don't have to set a global. A script is therefore no longer needed. You could put his portal directly on the main Customer layout and it would still work, even when flipping to the next record using the status area. This eliminates a navigation problem.

As far as the unstored calculation causing slowness, it might appear that my method was more direct. But there is another factor. I've read that because of the way Server 7 distributes the workload, using a global field in a calculation is going to be slower; something about having to pass the local global to Server for it to evaluate the calculation, whereas it doesn't have to do this for Ugo's calculation. Probably a small difference, but it tips the scale. I've thought of this also in regard to calendar and scheduling solutions, where a global is in many calculations and relationships, all on the same layout.

While I've used a self-relationship to mark either the first or the last record before pre-7, it was never like this; looking through it at another instance of the same table, to produce unique lines in a portal from Customers.

So, while going through an extra TO to filter records may have other uses, I was solving a problem that didn't need to exist (again).

BTW, you will want another plain Product TO off of plain Line Items, for adding products.