Script help please.

[nightdesigns]

Here's my situation. We have a DB or items listed on ebay. If you're familiar with e-bay, then you know that each item is given an ID number, and this is how we do the majority of the tracking in our database. When an item doesn't sell, the record is set as "completed" (for tracking refrence), and is duplicated and set to "pending repost", etc, etc. The ID number from the completed auction is copied into an "old id" field in the pending repost record. I'd like a way to essentially see how many times an auction has been posted w/out selling.

The idea is to take the old Id number, look up the previous, if there's a number in that old id field, repeat the number of times necessary until the old ID number is empty, counting all along the way.

Things to keep in mind. Not all records have an ID and old ID number (posted 0 times). Some have an ID number only (posted 1 time). some only have an Old ID number (posted 1 time). Each ID number is only used once.

Now, what's the best way to do this. I've come up with to ways.

1) just keep doing find steps, and then "find" the original one and paste in the counter. Problem with not every record having an ID number, how do you get back to the start on some?

2) Create a portal ID to old ID (running off of globals) and keep searchign that way.

I toyed around with the second way. I have something, but i'm having some counting issues. i'm not on my laptop at the moment, but i'll post that file so you can see the issues i'm having.

Any other ideas?

Thanks

-j

[Ugo DI LUCA]

Hi,

I'm sure you have somewhere another UNIQUE ID for that Item...How would you do otherwise ?

Therefore :

Count(SelfjoinOnUnique_ID::Old_ID) should give you the correct number of occurence of a given ID in the Old ID field for that Unique ID.

I assume that if an item has a ID and no Old ID, this means it is currently on sale....

[Fitch]

I'd recommend you create a field for your own internal tracking number of a given item. This might be slightly tricky, since you'd want to auto-enter a number, but use the same number when you duplicate an item. You'd need to duplicate via a script, and then set the item id to the id of the previous record. OK, so it's not all that tricky.

Once you've got that, you create a self-join relationship by item number. The total number of times an item was listed would be:

Count(selfbyitem::ebaynumber)

... and the number of times it was listed and didn't sell would be that calc minus one.

FYI the reason I used the ebay number in the calc is so you can have an item that wasn't listed yet on ebay, and it won't be counted in the calc -- assuming that's what you want. To get a count of all items, even those never listed, just count something that always has a value, such as the item id we just created.

[nightdesigns]

Unfortunatly, it's going on an existing database of 12000, so figuring out ID numbers by hand is a no no. if i can figure out how to attach a file, you can see a test Db i've been working on, and had some success, except for a few areas.

EDIT: attached below.

-j

[nightdesigns]

Edit: Updated with better script. This one looks like it works, anyone see why i shouldn't use it to do that job?

Thanks

-j

download.zip

[Ugo DI LUCA]

Hi,

Something mysterious here. How can you know which old ID and ID are related to the same item ? There must be a way to have a unique ID in order to identify the item, then count each occurence in the Ids field whether Old or new ID.

I have the feeling I'm missing something obvious here...Are your Old Ids unique ?

Is there any other way you could retrieve a Unique ID (as Tom suggested, you usually have your own) ? Why not use (even if it's tedious) the Item descripton ?

I'm really confused and will wait and see what solutions other may have in that case...

[nightdesigns]

When an item doesn't sell, it is duplicated, the ID on the new record goes to Old ID and clears out the ID field. Therefore, if an item hasn't sold, there's always a related record to it.

-j

[Ugo DI LUCA]

Hi,

Ex :

TV Set

Attempt 1 - Status - On Ebay - ID 0001 OLD ID "Empty"

Attempt 1- Status - Out Ebay - ID "-----" OLD ID 0001

Attempt 2 - Status - On Ebay - ID 0002 OLD ID "Empty"

Attempt 2- Status - Out Ebay - ID "-----" OLD ID 0002

Book

Attempt 1 - Status - On Ebay - ID 0005 OLD ID "Empty"

Attempt 1- Status - Out Ebay - ID "-----" OLD ID 0005

Attempt 2 - Status - On Ebay - ID 0009 OLD ID "Empty"

Attempt 2- Status - Out Ebay - ID "-----" OLD ID 0005

OK, So I how would we know that ID0005 was attached to the Book and ID0002 to the TV ?

I'm still confused. There's no magic here I think ! We need a Unique identificant (either a number, a description, or anything) in order to group the IDs together and count the attached Old and "new" Ids.

Except if the Old ID is Unique which not seems to be the case, as you duplicate the ebay ID (which changes obviously from one insertion to the other).

May be I'm just tired, so sorry if I'm being a pest !!

Second Attempt : ID0002 OLD ID 0001

Second Attempt Not Sold : ID "Empty" OLD ID 0002

[Ugo DI LUCA]

Just forget about the 2 last lines....just error while typing and previewing my answer.

In addition, you surely have a relationship from Old to New (ID0001::ID0001) but it's a one to one, so you could count the number of attempt needed for "All" your products on Ebay, but not for only one product. Still in my opinion though, waiting for others magicians.

[nightdesigns]

Ah, but there is a magic link there.

No ID is used more than once (unique ID from ebay).

Therefore:

no OLD ID is used more than once since it is based off of above.

Anyway, the script i created, did the job. Thanks for the help.

[Ugo DI LUCA]

Hi,

For my personal understanding, could you extend a little on this. I'm still stuck.

Are you saying that the Old_ID attached to an item is always the same, therefore the Old_ID (previous Unique_ID of ebay) is Unique, and you have i.e. 7 Old_ID Id0002 related to an ebay ID Id0002.

If not, would you mind posting a more detailled table, with some more records on it ? Thanks.

[nightdesigns]

Are you saying that the Old_ID attached to an item is always the same, therefore the Old_ID (previous Unique_ID of ebay) is Unique, and you have i.e. 7 Old_ID Id0002 related to an ebay ID Id0002.

exactly!

[Ugo DI LUCA]

So we finally found this Unique ID.....

.....So you can delete your script and use a calculation instead, that should be really more dynamic.

NbOfAttemptsForItem= Case(IsEmpty(Ebay ID), Count(SelfJoinOld_ID::Old_ID), Count(SelfJoinOld_ID::Old_ID)+1))