Cross-Reference Match two Portals

[LaRetta]

Goal: I need to cross-reference two different portal sets of data. This will take manual intervention and decision making. This example is based upon Pickup table (Main), LineItems (Portal1) and Returns (Portal2) but I'm keeping it generic because it will be used in other wonky situations that require manual decisions.

User will be presented with something similar to the demo I've attached. Their job is to view the first item in Portal2 and find it's match in Portal1. Only each staff person will know which Portal1 item to select - there are many factors (date purchased, sales reps handle things differently, etc). They will use the radio to filter Portal1 down to only possible matching entries. The filter is necessary because of the volume of matches produced in Portal 1.

They will then click on Portal2 to select that item (only the yellow fields have the scripts right now. I'll place transparent rectangles over them later), then click Portal1 entry to apply it to. The Item MUST match (and should be validated, in this example on Item) but the quantity may not match.

If quantity in Portal2 is greater than Portal1, allow Portal1 to be set anyway but ONLY with the max quantity in Portal1, ie, you can't return more of a product than what was sold). Each portal will (or should) display the sum from the other and validation should stop allowing Portal2 item from being applied if it reaches 0. Ideally, it would be nice if it disappears completely from Portal2 when used up.

I need to xref the PortalIDs as multiline. In this way, a later relationship (already in place) will allow GTRR from the LineItem to one or more returns; or one 'return line' to be applied to several different lineitem lines. And I need to only allow it if the quantities exist.

1) I've attempted to use ScriptParameters to handle it all but it fails. It doesn't set Portal1. Why? I thought I understood passing a parameter between scripts; maybe because I'm tired but it doesn't work.

2) I would consider a different relationship configuration - these TOs are only for this one-time normalization routine.

3) I've considered using copy/paste instead of ScriptParameters but feel I'll run into the same problems - a User will click out of sequence and set something incorrectly.

4) Allow an undo? Or maybe they should select (and highlight) one item from each portal (after filtering Portal1) and THEN let a script run it all - validating as it goes after receiving their confirmation that it is correct.

This wonky data is throwing our AR, Aging and other standardized data off because my new system (Pickups, Returns and Transactions) says these were never returned to stock. I need to get it matched and quickly. The example is weak in data. In reality, there may be 10-30 possible matches in Portal1 to choose from - all sold on different dates.

I hope I'm clear and I hope someone is willing to give me ANY input on it.

LaRetta

[LaRetta]

Goal: I need to cross-reference two different portal sets of data. This will take manual intervention and decision making. This example is based upon Pickup table (Main), LineItems (Portal1) and Returns (Portal2) but I'm keeping it generic because it will be used in other wonky situations that require manual decisions.

User will be presented with something similar to the demo I've attached. Their job is to view the first item in Portal2 and find it's match in Portal1. Only each staff person will know which Portal1 item to select - there are many factors (date purchased, sales reps handle things differently, etc). They will use the radio to filter Portal1 down to only possible matching entries. The filter is necessary because of the volume of matches produced in Portal 1.

They will then click on Portal2 to select that item (only the yellow fields have the scripts right now. I'll place transparent rectangles over them later), then click Portal1 entry to apply it to. The Item MUST match (and should be validated, in this example on Item) but the quantity may not match.

If quantity in Portal2 is greater than Portal1, allow Portal1 to be set anyway but ONLY with the max quantity in Portal1, ie, you can't return more of a product than what was sold). Each portal will (or should) display the sum from the other and validation should stop allowing Portal2 item from being applied if it reaches 0. Ideally, it would be nice if it disappears completely from Portal2 when used up.

I need to xref the PortalIDs as multiline. In this way, a later relationship (already in place) will allow GTRR from the LineItem to one or more returns; or one 'return line' to be applied to several different lineitem lines. And I need to only allow it if the quantities exist.

1) I've attempted to use ScriptParameters to handle it all but it fails. It doesn't set Portal1. Why? I thought I understood passing a parameter between scripts; maybe because I'm tired but it doesn't work.

2) I would consider a different relationship configuration - these TOs are only for this one-time normalization routine.

3) I've considered using copy/paste instead of ScriptParameters but feel I'll run into the same problems - a User will click out of sequence and set something incorrectly.

4) Allow an undo? Or maybe they should select (and highlight) one item from each portal (after filtering Portal1) and THEN let a script run it all - validating as it goes after receiving their confirmation that it is correct.

This wonky data is throwing our AR, Aging and other standardized data off because my new system (Pickups, Returns and Transactions) says these were never returned to stock. I need to get it matched and quickly. The example is weak in data. In reality, there may be 10-30 possible matches in Portal1 to choose from - all sold on different dates.

I hope I'm clear and I hope someone is willing to give me ANY input on it.

LaRetta

[LaRetta]

Goal: I need to cross-reference two different portal sets of data. This will take manual intervention and decision making. This example is based upon Pickup table (Main), LineItems (Portal1) and Returns (Portal2) but I'm keeping it generic because it will be used in other wonky situations that require manual decisions.

User will be presented with something similar to the demo I've attached. Their job is to view the first item in Portal2 and find it's match in Portal1. Only each staff person will know which Portal1 item to select - there are many factors (date purchased, sales reps handle things differently, etc). They will use the radio to filter Portal1 down to only possible matching entries. The filter is necessary because of the volume of matches produced in Portal 1.

They will then click on Portal2 to select that item (only the yellow fields have the scripts right now. I'll place transparent rectangles over them later), then click Portal1 entry to apply it to. The Item MUST match (and should be validated, in this example on Item) but the quantity may not match.

If quantity in Portal2 is greater than Portal1, allow Portal1 to be set anyway but ONLY with the max quantity in Portal1, ie, you can't return more of a product than what was sold). Each portal will (or should) display the sum from the other and validation should stop allowing Portal2 item from being applied if it reaches 0. Ideally, it would be nice if it disappears completely from Portal2 when used up.

I need to xref the PortalIDs as multiline. In this way, a later relationship (already in place) will allow GTRR from the LineItem to one or more returns; or one 'return line' to be applied to several different lineitem lines. And I need to only allow it if the quantities exist.

1) I've attempted to use ScriptParameters to handle it all but it fails. It doesn't set Portal1. Why? I thought I understood passing a parameter between scripts; maybe because I'm tired but it doesn't work.

2) I would consider a different relationship configuration - these TOs are only for this one-time normalization routine.

3) I've considered using copy/paste instead of ScriptParameters but feel I'll run into the same problems - a User will click out of sequence and set something incorrectly.

4) Allow an undo? Or maybe they should select (and highlight) one item from each portal (after filtering Portal1) and THEN let a script run it all - validating as it goes after receiving their confirmation that it is correct.

This wonky data is throwing our AR, Aging and other standardized data off because my new system (Pickups, Returns and Transactions) says these were never returned to stock. I need to get it matched and quickly. The example is weak in data. In reality, there may be 10-30 possible matches in Portal1 to choose from - all sold on different dates.

I hope I'm clear and I hope someone is willing to give me ANY input on it.

LaRetta

MatchPortals.zip

[Ugo DI LUCA]

Hi LaRetta,

Before I try to understand the dwingling picking process and parameters, may I comment on the structure you've chosen or inherited.

"Only each staff person will know which Portal1 item to select - there are many factors (date purchased, sales reps handle things differently, etc)."

While there is a very good reason to separate Sales LineItems from Purchased LineItems, there aren't any to separate the Returned items from the Sold ones.

A return on Sales is still related to a Sale operation, and should be treated exactly the same way as Deliveries which finally determines an Invoice. In this case, returns determine refunds. If it happens that the returns should be related to more than one operation, then this *really* is a good reason to not separate.

You'd need an internal ID into the LineItems, just as for systems where different operations are completed from a quote to an invoice, and where more than one delivery could be splitted into one invoice, or where one Customer order could be splitted into separate deliveries.

This kind of "FamilyID" is very easy to build with FM7, and helps tracking any sale operation for a single product or a single customer.

So your staff wouldn't even play with data to identify from which sale this item was returned, as the return itself should be related to a Sale operation from the very start.

In fact, the process should be reversed compared to yours, as the staff should enter the customerID and Product being returned. From there, they should see the different Sale Operation for this Customer and Product, and therefore determines to which sale(s) operations these returns should be related.

My 2 cents.

[Ugo DI LUCA]

Hi LaRetta,

Before I try to understand the dwingling picking process and parameters, may I comment on the structure you've chosen or inherited.

"Only each staff person will know which Portal1 item to select - there are many factors (date purchased, sales reps handle things differently, etc)."

While there is a very good reason to separate Sales LineItems from Purchased LineItems, there aren't any to separate the Returned items from the Sold ones.

A return on Sales is still related to a Sale operation, and should be treated exactly the same way as Deliveries which finally determines an Invoice. In this case, returns determine refunds. If it happens that the returns should be related to more than one operation, then this *really* is a good reason to not separate.

You'd need an internal ID into the LineItems, just as for systems where different operations are completed from a quote to an invoice, and where more than one delivery could be splitted into one invoice, or where one Customer order could be splitted into separate deliveries.

This kind of "FamilyID" is very easy to build with FM7, and helps tracking any sale operation for a single product or a single customer.

So your staff wouldn't even play with data to identify from which sale this item was returned, as the return itself should be related to a Sale operation from the very start.

In fact, the process should be reversed compared to yours, as the staff should enter the customerID and Product being returned. From there, they should see the different Sale Operation for this Customer and Product, and therefore determines to which sale(s) operations these returns should be related.

My 2 cents.

[Ugo DI LUCA]

Hi LaRetta,

Before I try to understand the dwingling picking process and parameters, may I comment on the structure you've chosen or inherited.

"Only each staff person will know which Portal1 item to select - there are many factors (date purchased, sales reps handle things differently, etc)."

While there is a very good reason to separate Sales LineItems from Purchased LineItems, there aren't any to separate the Returned items from the Sold ones.

A return on Sales is still related to a Sale operation, and should be treated exactly the same way as Deliveries which finally determines an Invoice. In this case, returns determine refunds. If it happens that the returns should be related to more than one operation, then this *really* is a good reason to not separate.

You'd need an internal ID into the LineItems, just as for systems where different operations are completed from a quote to an invoice, and where more than one delivery could be splitted into one invoice, or where one Customer order could be splitted into separate deliveries.

This kind of "FamilyID" is very easy to build with FM7, and helps tracking any sale operation for a single product or a single customer.

So your staff wouldn't even play with data to identify from which sale this item was returned, as the return itself should be related to a Sale operation from the very start.

In fact, the process should be reversed compared to yours, as the staff should enter the customerID and Product being returned. From there, they should see the different Sale Operation for this Customer and Product, and therefore determines to which sale(s) operations these returns should be related.

My 2 cents.

[LaRetta]

Thank you Ugo. You being in sales you'll understand this: How in the world we can make the following determinations if we don't match these returns to their original purchase if it WASN'T tracked originally?

1) If it is a first sale, Reps will get paid a flat rate for the Sales Invoice Amount only. No commission for the various items sold (bottle count commission).

2) First sales is determined if no sales has been made to a new customer (or existing customer) within two years from the date of each sale.

3) Bottle count commissions are paid on CERTAIN products, but ONLY if those products were not returned or otherwise forced to be returned due to Rep responsibility (another thing that's being flagged in this cleanup process).

4) Bottle count commissions are NOT paid on First Sales (see #1 & 2 above).

5) Returns are applied depending up the rules from 1-4 and accordingly BACKED OUT. I can't back out returns without knowing which LineItem they originally were. And that original LineItem will then tell me how to treat the return. We simply will NOT know whether to credit it as a bottle commission or apply the amount as a credit to First Sale commissions.

6) Believe it or not, I have found examples where the same product (sold only once) was returned 8 times. They were returned unethically and the returnee made a profit. New process knows what goes out (because every stock lineitem has Lot#/ExpDate and, once returned, the LineItem is flagged so it can't be returned again.

7) Not all of these products come back ... and ofttimes MORE comes back than was originally sent. And sometimes product comes back damaged. Or is 6 years older than they've ever purchased. And the list goes on ...

NEW PROCESS:

1) Pickup Request Form created by Rep when Customer calls. Rep selects a LineItem which creates a Return Item (the reverse from the demo) and indicates in Returns how many of the original quantity is expected to be returned. LineItem receives ReturnID. It needs nothing else because the quantity to be returned is tracked in Returns.

2) This is POTENTIAL return - not placing back in stock and there is very big distinction here. Since Return has the LineItemID, it doesn't need to track the ExpDate or Lot# (only the LineItemID and Quantity).

3) Return gets the LineItemID so that, if the stock returned matches the Lot#/ExpDate, it writes a new record to LineItem but ONLY if it can be a stock item again and it bases the new record on the old LineItemID (price and ExpDate/Lot#). If it is not resellable (damanged or outdated) it is written off and the Customer gets a credit for THAT Return item only but the original LineItem maintains the ReturnID because, even though the product was NOT really returned to inventory, credit was applied and that LineItem can't be returned again.

Same holds true if they call in to return a product because it's damaged and we tell them not to bother returning it. It nonetheless is flagged on the LineItem but treated as a straight credit (in the Return system). Bottle commissions demands it, as well as other processes. This provides a multiline in the LineItem of Returns or Credits applied against each product. This may not be necessary, but until we match all the old stuff, I thought it best to cross-reference them and the one field with multiline takes little resources. I have a note to consider dropping this multiline once I know the process is fully solidified and that the LineItemID in Return table is sufficient. And for the backward match, I think it's important.

Creation of the Pickup Request generates the Electronic Call Tag. The weight is pulled from the Products table (through the LineItem table) to the Return items x quantity (expected return) to determine overall Pickup weight. The flags on the LineItems are simply flags until the product comes back. If the Return doesn't include a product indicated on the original Return request, it's flag is removed from the LineItem and the shipper indicates that product wasn't returned on the Return comment line but that relationship to the LineItem must be preserved in case of questions.

These Returns were Excel spreadsheets. They don't even have an Invoice Number to match to. I need to plug the backward holes of information to make the new system work correctly and the backward match will span 4 years. That's how far we take products back. Each Rep has their notes on the Customer (and other reference fields) which will show on this demo layout (to assist them) and will allow them to pretty-accurately make these matches. Truthfully, I shouldn't have even mentioned that it was Products/Returns structure because it isn't. It's (as you've indicated, the reverse), ie, the OLD process trying to merge with the new. The new system will plug these very same holes.

Simply, my request for assistance on this demo gears towards matching two portals of like data to clean up a mess. Shippers are guestimating ExpDates back through the LineItems now. Once matched, the items will be moved to LineItems. But as they are cleaning it up (there are 5,000 returns), I don't want them to over return to the same LineItem. This is IMPORTANT and the only reason I've attempted to display the sums for them. So system (or they) can easily see the balances on both sides as they work and maybe so system can STOP them from making errors in it.

There are other such cleanups which need to occur and I am hoping to use this same Cross-Reference 2-Portal selection process to solve them as well so I can bring this business totally into normalization and current process.

So your staff wouldn't even play with data to identify from which sale this item was returned, as the return itself should be related to a Sale operation from the very start.

It wasn't.

...the staff should enter the customerID and Product being returned. From there, they should see the different Sale Operation for this Customer and Product, and therefore determines to which sale(s) operations these returns should be related.

And that's exactly what the new Pickup form does, thanks in part to your help on that prior thread, Ugo. We are in total agreement.

Don't mix that demo with my regular LineItems/Returns process. They will be handled a bit differently as I've just described (in my usual lengthy style). Does that explain it better?

LaRetta

[LaRetta]

Thank you Ugo. You being in sales you'll understand this: How in the world we can make the following determinations if we don't match these returns to their original purchase if it WASN'T tracked originally?

1) If it is a first sale, Reps will get paid a flat rate for the Sales Invoice Amount only. No commission for the various items sold (bottle count commission).

2) First sales is determined if no sales has been made to a new customer (or existing customer) within two years from the date of each sale.

3) Bottle count commissions are paid on CERTAIN products, but ONLY if those products were not returned or otherwise forced to be returned due to Rep responsibility (another thing that's being flagged in this cleanup process).

4) Bottle count commissions are NOT paid on First Sales (see #1 & 2 above).

5) Returns are applied depending up the rules from 1-4 and accordingly BACKED OUT. I can't back out returns without knowing which LineItem they originally were. And that original LineItem will then tell me how to treat the return. We simply will NOT know whether to credit it as a bottle commission or apply the amount as a credit to First Sale commissions.

6) Believe it or not, I have found examples where the same product (sold only once) was returned 8 times. They were returned unethically and the returnee made a profit. New process knows what goes out (because every stock lineitem has Lot#/ExpDate and, once returned, the LineItem is flagged so it can't be returned again.

7) Not all of these products come back ... and ofttimes MORE comes back than was originally sent. And sometimes product comes back damaged. Or is 6 years older than they've ever purchased. And the list goes on ...

NEW PROCESS:

1) Pickup Request Form created by Rep when Customer calls. Rep selects a LineItem which creates a Return Item (the reverse from the demo) and indicates in Returns how many of the original quantity is expected to be returned. LineItem receives ReturnID. It needs nothing else because the quantity to be returned is tracked in Returns.

2) This is POTENTIAL return - not placing back in stock and there is very big distinction here. Since Return has the LineItemID, it doesn't need to track the ExpDate or Lot# (only the LineItemID and Quantity).

3) Return gets the LineItemID so that, if the stock returned matches the Lot#/ExpDate, it writes a new record to LineItem but ONLY if it can be a stock item again and it bases the new record on the old LineItemID (price and ExpDate/Lot#). If it is not resellable (damanged or outdated) it is written off and the Customer gets a credit for THAT Return item only but the original LineItem maintains the ReturnID because, even though the product was NOT really returned to inventory, credit was applied and that LineItem can't be returned again.

Same holds true if they call in to return a product because it's damaged and we tell them not to bother returning it. It nonetheless is flagged on the LineItem but treated as a straight credit (in the Return system). Bottle commissions demands it, as well as other processes. This provides a multiline in the LineItem of Returns or Credits applied against each product. This may not be necessary, but until we match all the old stuff, I thought it best to cross-reference them and the one field with multiline takes little resources. I have a note to consider dropping this multiline once I know the process is fully solidified and that the LineItemID in Return table is sufficient. And for the backward match, I think it's important.

Creation of the Pickup Request generates the Electronic Call Tag. The weight is pulled from the Products table (through the LineItem table) to the Return items x quantity (expected return) to determine overall Pickup weight. The flags on the LineItems are simply flags until the product comes back. If the Return doesn't include a product indicated on the original Return request, it's flag is removed from the LineItem and the shipper indicates that product wasn't returned on the Return comment line but that relationship to the LineItem must be preserved in case of questions.

These Returns were Excel spreadsheets. They don't even have an Invoice Number to match to. I need to plug the backward holes of information to make the new system work correctly and the backward match will span 4 years. That's how far we take products back. Each Rep has their notes on the Customer (and other reference fields) which will show on this demo layout (to assist them) and will allow them to pretty-accurately make these matches. Truthfully, I shouldn't have even mentioned that it was Products/Returns structure because it isn't. It's (as you've indicated, the reverse), ie, the OLD process trying to merge with the new. The new system will plug these very same holes.

Simply, my request for assistance on this demo gears towards matching two portals of like data to clean up a mess. Shippers are guestimating ExpDates back through the LineItems now. Once matched, the items will be moved to LineItems. But as they are cleaning it up (there are 5,000 returns), I don't want them to over return to the same LineItem. This is IMPORTANT and the only reason I've attempted to display the sums for them. So system (or they) can easily see the balances on both sides as they work and maybe so system can STOP them from making errors in it.

There are other such cleanups which need to occur and I am hoping to use this same Cross-Reference 2-Portal selection process to solve them as well so I can bring this business totally into normalization and current process.

So your staff wouldn't even play with data to identify from which sale this item was returned, as the return itself should be related to a Sale operation from the very start.

It wasn't.

...the staff should enter the customerID and Product being returned. From there, they should see the different Sale Operation for this Customer and Product, and therefore determines to which sale(s) operations these returns should be related.

And that's exactly what the new Pickup form does, thanks in part to your help on that prior thread, Ugo. We are in total agreement.

Don't mix that demo with my regular LineItems/Returns process. They will be handled a bit differently as I've just described (in my usual lengthy style). Does that explain it better?

LaRetta

[LaRetta]

Thank you Ugo. You being in sales you'll understand this: How in the world we can make the following determinations if we don't match these returns to their original purchase if it WASN'T tracked originally?

1) If it is a first sale, Reps will get paid a flat rate for the Sales Invoice Amount only. No commission for the various items sold (bottle count commission).

2) First sales is determined if no sales has been made to a new customer (or existing customer) within two years from the date of each sale.

3) Bottle count commissions are paid on CERTAIN products, but ONLY if those products were not returned or otherwise forced to be returned due to Rep responsibility (another thing that's being flagged in this cleanup process).

4) Bottle count commissions are NOT paid on First Sales (see #1 & 2 above).

5) Returns are applied depending up the rules from 1-4 and accordingly BACKED OUT. I can't back out returns without knowing which LineItem they originally were. And that original LineItem will then tell me how to treat the return. We simply will NOT know whether to credit it as a bottle commission or apply the amount as a credit to First Sale commissions.

6) Believe it or not, I have found examples where the same product (sold only once) was returned 8 times. They were returned unethically and the returnee made a profit. New process knows what goes out (because every stock lineitem has Lot#/ExpDate and, once returned, the LineItem is flagged so it can't be returned again.

7) Not all of these products come back ... and ofttimes MORE comes back than was originally sent. And sometimes product comes back damaged. Or is 6 years older than they've ever purchased. And the list goes on ...

NEW PROCESS:

1) Pickup Request Form created by Rep when Customer calls. Rep selects a LineItem which creates a Return Item (the reverse from the demo) and indicates in Returns how many of the original quantity is expected to be returned. LineItem receives ReturnID. It needs nothing else because the quantity to be returned is tracked in Returns.

2) This is POTENTIAL return - not placing back in stock and there is very big distinction here. Since Return has the LineItemID, it doesn't need to track the ExpDate or Lot# (only the LineItemID and Quantity).

3) Return gets the LineItemID so that, if the stock returned matches the Lot#/ExpDate, it writes a new record to LineItem but ONLY if it can be a stock item again and it bases the new record on the old LineItemID (price and ExpDate/Lot#). If it is not resellable (damanged or outdated) it is written off and the Customer gets a credit for THAT Return item only but the original LineItem maintains the ReturnID because, even though the product was NOT really returned to inventory, credit was applied and that LineItem can't be returned again.

Same holds true if they call in to return a product because it's damaged and we tell them not to bother returning it. It nonetheless is flagged on the LineItem but treated as a straight credit (in the Return system). Bottle commissions demands it, as well as other processes. This provides a multiline in the LineItem of Returns or Credits applied against each product. This may not be necessary, but until we match all the old stuff, I thought it best to cross-reference them and the one field with multiline takes little resources. I have a note to consider dropping this multiline once I know the process is fully solidified and that the LineItemID in Return table is sufficient. And for the backward match, I think it's important.

Creation of the Pickup Request generates the Electronic Call Tag. The weight is pulled from the Products table (through the LineItem table) to the Return items x quantity (expected return) to determine overall Pickup weight. The flags on the LineItems are simply flags until the product comes back. If the Return doesn't include a product indicated on the original Return request, it's flag is removed from the LineItem and the shipper indicates that product wasn't returned on the Return comment line but that relationship to the LineItem must be preserved in case of questions.

These Returns were Excel spreadsheets. They don't even have an Invoice Number to match to. I need to plug the backward holes of information to make the new system work correctly and the backward match will span 4 years. That's how far we take products back. Each Rep has their notes on the Customer (and other reference fields) which will show on this demo layout (to assist them) and will allow them to pretty-accurately make these matches. Truthfully, I shouldn't have even mentioned that it was Products/Returns structure because it isn't. It's (as you've indicated, the reverse), ie, the OLD process trying to merge with the new. The new system will plug these very same holes.

Simply, my request for assistance on this demo gears towards matching two portals of like data to clean up a mess. Shippers are guestimating ExpDates back through the LineItems now. Once matched, the items will be moved to LineItems. But as they are cleaning it up (there are 5,000 returns), I don't want them to over return to the same LineItem. This is IMPORTANT and the only reason I've attempted to display the sums for them. So system (or they) can easily see the balances on both sides as they work and maybe so system can STOP them from making errors in it.

There are other such cleanups which need to occur and I am hoping to use this same Cross-Reference 2-Portal selection process to solve them as well so I can bring this business totally into normalization and current process.

So your staff wouldn't even play with data to identify from which sale this item was returned, as the return itself should be related to a Sale operation from the very start.

It wasn't.

...the staff should enter the customerID and Product being returned. From there, they should see the different Sale Operation for this Customer and Product, and therefore determines to which sale(s) operations these returns should be related.

And that's exactly what the new Pickup form does, thanks in part to your help on that prior thread, Ugo. We are in total agreement.

Don't mix that demo with my regular LineItems/Returns process. They will be handled a bit differently as I've just described (in my usual lengthy style). Does that explain it better?

LaRetta

[Ugo DI LUCA]

Hi,

I understand this system is a temporary "tool" which intent is to clean-up an existing set. I am in perfect agreement that Return(s) should match Sale(s), whatever the final consequences on Stock or Payments may finally be.

Even if 7 has a rather flexible Relational Architecture, its structure should reflect the business flaws. My point is that they should belong to the same Source Table, call it LineItems_Sales,

Your "cleaning" process could then take place on the 2 portals shown on your demo, but these portals would just be based on 2 separate TO's of the same Base Table.

Quotes, Customer Orders, Deliveries, Returns and Invoice are separate Tables, each with a separate ID and related to the same LineItems by this ID.

The fact that within a same Return, a product could match several Sales is just advocating for such a structure.

What you need is that InternalID in the Line Items that allows to tie together several Sale Operations.

A Return should not only match an Invoice but rather all preceding operations that led to that invoice, specially when a Customer Order may be delivered in several shipments.

This sometimes implies that when a customer brings back products, a 20 item returns may finally be splitted in 2 lines in the Line Items.

15 referencing Delivery# D.1255 + 5 referencing Delivery#D.14755

As Delivery#D.1255 is related throuh an internal ID to CustomerOrder#C.1487 and Invoice#I.4559, you'd have a whole visualization of the process at the end through this ID.

Sorry I didn't have time to check in depth your sampler.

[Ugo DI LUCA]

Hi,

I understand this system is a temporary "tool" which intent is to clean-up an existing set. I am in perfect agreement that Return(s) should match Sale(s), whatever the final consequences on Stock or Payments may finally be.

Even if 7 has a rather flexible Relational Architecture, its structure should reflect the business flaws. My point is that they should belong to the same Source Table, call it LineItems_Sales,

Your "cleaning" process could then take place on the 2 portals shown on your demo, but these portals would just be based on 2 separate TO's of the same Base Table.

Quotes, Customer Orders, Deliveries, Returns and Invoice are separate Tables, each with a separate ID and related to the same LineItems by this ID.

The fact that within a same Return, a product could match several Sales is just advocating for such a structure.

What you need is that InternalID in the Line Items that allows to tie together several Sale Operations.

A Return should not only match an Invoice but rather all preceding operations that led to that invoice, specially when a Customer Order may be delivered in several shipments.

This sometimes implies that when a customer brings back products, a 20 item returns may finally be splitted in 2 lines in the Line Items.

15 referencing Delivery# D.1255 + 5 referencing Delivery#D.14755

As Delivery#D.1255 is related throuh an internal ID to CustomerOrder#C.1487 and Invoice#I.4559, you'd have a whole visualization of the process at the end through this ID.

Sorry I didn't have time to check in depth your sampler.

[Ugo DI LUCA]

Hi,

I understand this system is a temporary "tool" which intent is to clean-up an existing set. I am in perfect agreement that Return(s) should match Sale(s), whatever the final consequences on Stock or Payments may finally be.

Even if 7 has a rather flexible Relational Architecture, its structure should reflect the business flaws. My point is that they should belong to the same Source Table, call it LineItems_Sales,

Your "cleaning" process could then take place on the 2 portals shown on your demo, but these portals would just be based on 2 separate TO's of the same Base Table.

Quotes, Customer Orders, Deliveries, Returns and Invoice are separate Tables, each with a separate ID and related to the same LineItems by this ID.

The fact that within a same Return, a product could match several Sales is just advocating for such a structure.

What you need is that InternalID in the Line Items that allows to tie together several Sale Operations.

A Return should not only match an Invoice but rather all preceding operations that led to that invoice, specially when a Customer Order may be delivered in several shipments.

This sometimes implies that when a customer brings back products, a 20 item returns may finally be splitted in 2 lines in the Line Items.

15 referencing Delivery# D.1255 + 5 referencing Delivery#D.14755

As Delivery#D.1255 is related throuh an internal ID to CustomerOrder#C.1487 and Invoice#I.4559, you'd have a whole visualization of the process at the end through this ID.

Sorry I didn't have time to check in depth your sampler.

[LaRetta]

"that led to that invoice, specially when a Customer Order may be delivered in several shipments."

We don't have SEVERAL sale operations! We have ONE SALE operation. We would have accounted for that if need be. There are no 'prior' activities on these orders - no quotes - nada. We don't back order. This is overkill for us and not something Management is going to change now. It is simply not necessary for our business. We don't even have Orders and then Invoices - our Orders ARE are Invoices. There is no process to track except a possible return. Period. And our structure accounts for this.

"Your "cleaning" process could then take place on the 2 portals shown on your demo, but these portals would just be based on 2 separate TO's of the same Base Table."

They currently are separate because they were from two different sources. They never HAVE been together yet. They will be once they are matched. But I didn't see the point of dumping unrelated data into my LineItems until they were matched properly.

"a 20 item returns may finally be splitted in 2 lines in the Line Items.

15 referencing Delivery# D.1255 + 5 referencing Delivery#D.14755"

Exactly - and this is precisely what I have in place, what I explained and what the demo shows.

And it doesn't matter whether I'm referencing the same LineItem table or two different tables - the 2-portal theory still applies and is valid for the demonstration and match process I am attempting and that is exactly what I put in my demo file.

I have the internal ties in place now, Ugo. This demo, and pulling together segmented data is an outside piece. Well, now that I've spent all this time discussing my business logic (and our business decisions) with you, I guess I'll just solve this cross-reference process myself. I've certainly acomplished much more difficult situations; but never when this tired and just couldn't think it through properly.

'tis okay ... I'll figure it out after some rest. Thanks for all your suggestions anyway.

LaRetta

[LaRetta]

"that led to that invoice, specially when a Customer Order may be delivered in several shipments."

We don't have SEVERAL sale operations! We have ONE SALE operation. We would have accounted for that if need be. There are no 'prior' activities on these orders - no quotes - nada. We don't back order. This is overkill for us and not something Management is going to change now. It is simply not necessary for our business. We don't even have Orders and then Invoices - our Orders ARE are Invoices. There is no process to track except a possible return. Period. And our structure accounts for this.

"Your "cleaning" process could then take place on the 2 portals shown on your demo, but these portals would just be based on 2 separate TO's of the same Base Table."

They currently are separate because they were from two different sources. They never HAVE been together yet. They will be once they are matched. But I didn't see the point of dumping unrelated data into my LineItems until they were matched properly.

"a 20 item returns may finally be splitted in 2 lines in the Line Items.

15 referencing Delivery# D.1255 + 5 referencing Delivery#D.14755"

Exactly - and this is precisely what I have in place, what I explained and what the demo shows.

And it doesn't matter whether I'm referencing the same LineItem table or two different tables - the 2-portal theory still applies and is valid for the demonstration and match process I am attempting and that is exactly what I put in my demo file.

I have the internal ties in place now, Ugo. This demo, and pulling together segmented data is an outside piece. Well, now that I've spent all this time discussing my business logic (and our business decisions) with you, I guess I'll just solve this cross-reference process myself. I've certainly acomplished much more difficult situations; but never when this tired and just couldn't think it through properly.

'tis okay ... I'll figure it out after some rest. Thanks for all your suggestions anyway.

LaRetta

[LaRetta]

"that led to that invoice, specially when a Customer Order may be delivered in several shipments."

We don't have SEVERAL sale operations! We have ONE SALE operation. We would have accounted for that if need be. There are no 'prior' activities on these orders - no quotes - nada. We don't back order. This is overkill for us and not something Management is going to change now. It is simply not necessary for our business. We don't even have Orders and then Invoices - our Orders ARE are Invoices. There is no process to track except a possible return. Period. And our structure accounts for this.

"Your "cleaning" process could then take place on the 2 portals shown on your demo, but these portals would just be based on 2 separate TO's of the same Base Table."

They currently are separate because they were from two different sources. They never HAVE been together yet. They will be once they are matched. But I didn't see the point of dumping unrelated data into my LineItems until they were matched properly.

"a 20 item returns may finally be splitted in 2 lines in the Line Items.

15 referencing Delivery# D.1255 + 5 referencing Delivery#D.14755"

Exactly - and this is precisely what I have in place, what I explained and what the demo shows.

And it doesn't matter whether I'm referencing the same LineItem table or two different tables - the 2-portal theory still applies and is valid for the demonstration and match process I am attempting and that is exactly what I put in my demo file.

I have the internal ties in place now, Ugo. This demo, and pulling together segmented data is an outside piece. Well, now that I've spent all this time discussing my business logic (and our business decisions) with you, I guess I'll just solve this cross-reference process myself. I've certainly acomplished much more difficult situations; but never when this tired and just couldn't think it through properly.

'tis okay ... I'll figure it out after some rest. Thanks for all your suggestions anyway.

LaRetta

[Ugo DI LUCA]

Hi again,

I meant I downloaded and looked at the demo, but not in depth so far. How could have I identified the 2 Tables otherwise ?

I was waiting for your answer on the first post, in case you had changed your minds which would have slightly changed the dwingling process, as referencing 2 Tables is different than referencing 2 TO's.

Well, I know you can solve it.

[Ugo DI LUCA]

Hi again,

I meant I downloaded and looked at the demo, but not in depth so far. How could have I identified the 2 Tables otherwise ?

I was waiting for your answer on the first post, in case you had changed your minds which would have slightly changed the dwingling process, as referencing 2 Tables is different than referencing 2 TO's.

Well, I know you can solve it.

[Ugo DI LUCA]

Hi again,

I meant I downloaded and looked at the demo, but not in depth so far. How could have I identified the 2 Tables otherwise ?

I was waiting for your answer on the first post, in case you had changed your minds which would have slightly changed the dwingling process, as referencing 2 Tables is different than referencing 2 TO's.

Well, I know you can solve it.

[comment]

I cannot offer any advice on strategy, but technique-wise, your script Set Portal 1 Item is not getting a script parameter.

You specified Get ( ScriptParameter ) as a script parameter in a button. But at the time the button is pressed, (a) there is no script running, and (: even if a script were running, your button is programmed to exit the current script.

If you were to pause the script Grab Return Item From Portal 2, AND change the button calling the script Set Portal 1 Item to Pause or Resume current script, then there would still be a script running when the button calls the new script, and Get ( ScriptParameter ) would mean something.

[comment]

I cannot offer any advice on strategy, but technique-wise, your script Set Portal 1 Item is not getting a script parameter.

You specified Get ( ScriptParameter ) as a script parameter in a button. But at the time the button is pressed, (a) there is no script running, and (: even if a script were running, your button is programmed to exit the current script.

If you were to pause the script Grab Return Item From Portal 2, AND change the button calling the script Set Portal 1 Item to Pause or Resume current script, then there would still be a script running when the button calls the new script, and Get ( ScriptParameter ) would mean something.

[comment]

I cannot offer any advice on strategy, but technique-wise, your script Set Portal 1 Item is not getting a script parameter.

You specified Get ( ScriptParameter ) as a script parameter in a button. But at the time the button is pressed, (a) there is no script running, and (: even if a script were running, your button is programmed to exit the current script.

If you were to pause the script Grab Return Item From Portal 2, AND change the button calling the script Set Portal 1 Item to Pause or Resume current script, then there would still be a script running when the button calls the new script, and Get ( ScriptParameter ) would mean something.

[LaRetta]

Hi Ugo,

...as referencing 2 Tables is different than referencing 2 TO's.

Nah! With FM7, you don't reference tables. You always reference TOs. Period. It doesn't matter where they come from - self-join, another table or another file - the process (and structure) would have been the same regardless.

I was waiting for your answer on the first post, in case you had changed your minds which would have slightly changed the dwingling process ...

Procedural issues of the type you've raised are Business Management decisions and are not made lightly or changed spur-of-the-moment. You and I have gotten bogged down in these types of discussions before ... you trying to change our business process and me trying to explain (defend) those very decisions.

My job as Business Consultant is to suggest improvements to business structure and all of the points you've raised were discussed in detail with Owners. Those decisions usually take months of heavy discussion, brain-strain and analysis.

Once those Business decisions are made, it is then my job as Developer to follow through on them and to provide an efficient program to achieve their goals. How I accomplish their goals (behind the scenes) is an FM thing ... and that's what I seek advice on here on Forums. I really DO appreciate your advice and help with FM questions I pose!

Comment hello!

Yes, of course! It's clear to me today after finally resting (and reading your words). And I KNEW that too. A silly mistake.

I still feel uneasy turning a point-and-click process over to Users in this way because they can potentially make several types of mistakes and proper backout would be iffy to script. I've decided (actually had a dream about it) to:

1) Allow them to highlight the first item in Portal 2. That will trigger the associated (matching) item display in Portal 1 (eliminating radio and the potential problem of them selecting an unassociated Item from Portal 1).

2) Then when they click the selected Item in Portal 1, script will fire and write the selected cross-reference match to a global gMessage which will display in a Custom Dialog asking for verification.

3) If Get(LastMessageChoice) = 2, script will clear gMessage, clear the highlights and clear the ScriptParameter.

4) If Get(LastMessageChoice) = 1, script will write the cross-reference, clear highlights and clear gMessage.

3) If the value of the Item in Portal 2 hits 0, the item relationship will be broken by same script and the item will disappear from Portal 2, allowing for the next Portal 2 Item selection. Script will also clear gItem so Items in Portal 1 will disappear. If Portal 2 Item is NOT at 0, script will clear Portal 1 highlight only and clear the second part of the Script Parameter string, allowing for another selection from Portal 1.

4) Once Portal 2 is empty (although the Items will still exist as Return Items associated to that Pickup Request (and matched to a LineItem), Staff can move to the next Pickup Request and repeat the process.

Thank you so much for responding. It's truly amazing what 6 hours sleep will afford one's mind.

LaRetta

[LaRetta]

Hi Ugo,

...as referencing 2 Tables is different than referencing 2 TO's.

Nah! With FM7, you don't reference tables. You always reference TOs. Period. It doesn't matter where they come from - self-join, another table or another file - the process (and structure) would have been the same regardless.

I was waiting for your answer on the first post, in case you had changed your minds which would have slightly changed the dwingling process ...

Procedural issues of the type you've raised are Business Management decisions and are not made lightly or changed spur-of-the-moment. You and I have gotten bogged down in these types of discussions before ... you trying to change our business process and me trying to explain (defend) those very decisions.

My job as Business Consultant is to suggest improvements to business structure and all of the points you've raised were discussed in detail with Owners. Those decisions usually take months of heavy discussion, brain-strain and analysis.

Once those Business decisions are made, it is then my job as Developer to follow through on them and to provide an efficient program to achieve their goals. How I accomplish their goals (behind the scenes) is an FM thing ... and that's what I seek advice on here on Forums. I really DO appreciate your advice and help with FM questions I pose!

Comment hello!

Yes, of course! It's clear to me today after finally resting (and reading your words). And I KNEW that too. A silly mistake.

I still feel uneasy turning a point-and-click process over to Users in this way because they can potentially make several types of mistakes and proper backout would be iffy to script. I've decided (actually had a dream about it) to:

1) Allow them to highlight the first item in Portal 2. That will trigger the associated (matching) item display in Portal 1 (eliminating radio and the potential problem of them selecting an unassociated Item from Portal 1).

2) Then when they click the selected Item in Portal 1, script will fire and write the selected cross-reference match to a global gMessage which will display in a Custom Dialog asking for verification.

3) If Get(LastMessageChoice) = 2, script will clear gMessage, clear the highlights and clear the ScriptParameter.

4) If Get(LastMessageChoice) = 1, script will write the cross-reference, clear highlights and clear gMessage.

3) If the value of the Item in Portal 2 hits 0, the item relationship will be broken by same script and the item will disappear from Portal 2, allowing for the next Portal 2 Item selection. Script will also clear gItem so Items in Portal 1 will disappear. If Portal 2 Item is NOT at 0, script will clear Portal 1 highlight only and clear the second part of the Script Parameter string, allowing for another selection from Portal 1.

4) Once Portal 2 is empty (although the Items will still exist as Return Items associated to that Pickup Request (and matched to a LineItem), Staff can move to the next Pickup Request and repeat the process.

Thank you so much for responding. It's truly amazing what 6 hours sleep will afford one's mind.

LaRetta

[LaRetta]

Hi Ugo,

...as referencing 2 Tables is different than referencing 2 TO's.

Nah! With FM7, you don't reference tables. You always reference TOs. Period. It doesn't matter where they come from - self-join, another table or another file - the process (and structure) would have been the same regardless.

I was waiting for your answer on the first post, in case you had changed your minds which would have slightly changed the dwingling process ...

Procedural issues of the type you've raised are Business Management decisions and are not made lightly or changed spur-of-the-moment. You and I have gotten bogged down in these types of discussions before ... you trying to change our business process and me trying to explain (defend) those very decisions.

My job as Business Consultant is to suggest improvements to business structure and all of the points you've raised were discussed in detail with Owners. Those decisions usually take months of heavy discussion, brain-strain and analysis.

Once those Business decisions are made, it is then my job as Developer to follow through on them and to provide an efficient program to achieve their goals. How I accomplish their goals (behind the scenes) is an FM thing ... and that's what I seek advice on here on Forums. I really DO appreciate your advice and help with FM questions I pose!

Comment hello!

Yes, of course! It's clear to me today after finally resting (and reading your words). And I KNEW that too. A silly mistake.

I still feel uneasy turning a point-and-click process over to Users in this way because they can potentially make several types of mistakes and proper backout would be iffy to script. I've decided (actually had a dream about it) to:

1) Allow them to highlight the first item in Portal 2. That will trigger the associated (matching) item display in Portal 1 (eliminating radio and the potential problem of them selecting an unassociated Item from Portal 1).

2) Then when they click the selected Item in Portal 1, script will fire and write the selected cross-reference match to a global gMessage which will display in a Custom Dialog asking for verification.

3) If Get(LastMessageChoice) = 2, script will clear gMessage, clear the highlights and clear the ScriptParameter.

4) If Get(LastMessageChoice) = 1, script will write the cross-reference, clear highlights and clear gMessage.

3) If the value of the Item in Portal 2 hits 0, the item relationship will be broken by same script and the item will disappear from Portal 2, allowing for the next Portal 2 Item selection. Script will also clear gItem so Items in Portal 1 will disappear. If Portal 2 Item is NOT at 0, script will clear Portal 1 highlight only and clear the second part of the Script Parameter string, allowing for another selection from Portal 1.

4) Once Portal 2 is empty (although the Items will still exist as Return Items associated to that Pickup Request (and matched to a LineItem), Staff can move to the next Pickup Request and repeat the process.

Thank you so much for responding. It's truly amazing what 6 hours sleep will afford one's mind.

LaRetta

[Ugo DI LUCA]

Procedural issues of the type you've raised are Business Management decisions and are not made lightly or changed spur-of-the-moment. You and I have gotten bogged down in these types of discussions before ... you trying to change our business process and me trying to explain (defend) those very decisions.

Right you are...:

Nah! With FM7, you don't reference tables. You always reference TOs. Period. It doesn't matter where they come from - self-join, another table or another file - the process (and structure) would have been the same regardless.

.

Not exactly. By working within a single Table, parsing/combining IDs happens to be different, and it is easier to explore the relationships in depth.

I agree with the rest of your post and won't be commenting on this any longer.

[Ugo DI LUCA]

Procedural issues of the type you've raised are Business Management decisions and are not made lightly or changed spur-of-the-moment. You and I have gotten bogged down in these types of discussions before ... you trying to change our business process and me trying to explain (defend) those very decisions.

Right you are...:

Nah! With FM7, you don't reference tables. You always reference TOs. Period. It doesn't matter where they come from - self-join, another table or another file - the process (and structure) would have been the same regardless.

.

Not exactly. By working within a single Table, parsing/combining IDs happens to be different, and it is easier to explore the relationships in depth.

I agree with the rest of your post and won't be commenting on this any longer.

[Ugo DI LUCA]

Procedural issues of the type you've raised are Business Management decisions and are not made lightly or changed spur-of-the-moment. You and I have gotten bogged down in these types of discussions before ... you trying to change our business process and me trying to explain (defend) those very decisions.

Right you are...:

Nah! With FM7, you don't reference tables. You always reference TOs. Period. It doesn't matter where they come from - self-join, another table or another file - the process (and structure) would have been the same regardless.

.

Not exactly. By working within a single Table, parsing/combining IDs happens to be different, and it is easier to explore the relationships in depth.

I agree with the rest of your post and won't be commenting on this any longer.

[LaRetta]

Comment, errr,

LaRetta boo boo'd and said ... 3) If Get(LastMessageChoice) = 2, script will clear gMessage, clear the highlights and clear the ScriptParameter.

I recognize I won't need to clear the Script Parameter because it will clear when the script halts.

Sometimes my fingers race ahead of my mind and sometimes my mind races ahead of my fingers but they both constantly race. And, overall, they work in conjunction pretty well. And overall, I get a lot done in short periods of time (even if sometimes wrong.)

I find myself correcting myself quite frequently - NO I DON'T!!!

LaRetta

[LaRetta]

Comment, errr,

LaRetta boo boo'd and said ... 3) If Get(LastMessageChoice) = 2, script will clear gMessage, clear the highlights and clear the ScriptParameter.

I recognize I won't need to clear the Script Parameter because it will clear when the script halts.

Sometimes my fingers race ahead of my mind and sometimes my mind races ahead of my fingers but they both constantly race. And, overall, they work in conjunction pretty well. And overall, I get a lot done in short periods of time (even if sometimes wrong.)

I find myself correcting myself quite frequently - NO I DON'T!!!

LaRetta

[LaRetta]

Comment, errr,

LaRetta boo boo'd and said ... 3) If Get(LastMessageChoice) = 2, script will clear gMessage, clear the highlights and clear the ScriptParameter.

I recognize I won't need to clear the Script Parameter because it will clear when the script halts.

Sometimes my fingers race ahead of my mind and sometimes my mind races ahead of my fingers but they both constantly race. And, overall, they work in conjunction pretty well. And overall, I get a lot done in short periods of time (even if sometimes wrong.)

I find myself correcting myself quite frequently - NO I DON'T!!!

LaRetta

[comment]

I don't see how you COULD clear the script parameter, if you wanted to.

[comment]

I don't see how you COULD clear the script parameter, if you wanted to.

[comment]

I don't see how you COULD clear the script parameter, if you wanted to.