Display only last in portal

[PiedPiper]

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.

[PiedPiper]

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.

[PiedPiper]

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.

[Vaughan]

Try the Last() function.

[Vaughan]

Try the Last() function.

[Vaughan]

Try the Last() function.

[Fenton]

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.

[Fenton]

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.

[Fenton]

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.

[comment]

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

[comment]

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

[comment]

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

LastPurchases.fp7.zip

[PiedPiper]

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

[PiedPiper]

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

[PiedPiper]

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

[Fenton]

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.

[Fenton]

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.

[Fenton]

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

[PiedPiper]

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

[PiedPiper]

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

[PiedPiper]

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

[PiedPiper]

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

[PiedPiper]

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

[PiedPiper]

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

[PiedPiper]

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.

[PiedPiper]

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.

[PiedPiper]

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.

[comment]

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.

[comment]

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.

[comment]

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.

[comment]

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)?

[comment]

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)?

[comment]

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)?

[Fenton]

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.

[Fenton]

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.