Sorting question

[Tom Cone Jr]

Re: Sorting question

Dylan, yes, I understand now. You don't care if there are multiple matches as you iterate through the possibilities... you just want the last matching distributor. Right?

[djtreble]

Re: Sorting question

exactly :D

Sorry I didn't put it that simply before...I have a tendency to make things over complicated for myself

[Mike Wilson]

Re: Sorting question

OK, see if this works for you.

[djtreble]

Re: Sorting question

Ok tried that but when I do the search it returns the same value all the time. And if I enter let's say 7 digits, it doesn't look for a distributor that matches it up to as many digits as the closest one has. So it's not doing the process of elimation I was talking about.

Also when i do 978 in the begining it returns another value, then when i entered in one value for some random one exactly as it is in the list, it returned with "no match found".

Can you explain what you did in it and also how I would implement it as a web based project.

The idea is I want to upload the attached excel sheet into the database, then let the program insert the correct distributer next to the isbn for a product sold. Then print it off as another excel sheet with this info in.

Attached is the document we recieve from Amazon , this is what I want to get back from Alpha but with the right distributors added next to their respective book

[Doug Page]

Re: Sorting question

Getting a little confused here but would this possibly work.

You have a table of your distributors and their code that you are looking for in the imported data. If you sort these by a decreasing value for this code you would get a list that would have the longest and highest value distributor codes at the top down to the shortest and lowest value distributor codes at the bottom.

Your imported data should have flag field in it that is false to begin with and a field for the distributor code. Now starting with the first distributor code from distributor code table above, filter the imported data with the first code (highest/longest code) and that doesn't have the flag equal to true. These should be all untouched records with the longest possible match of the distributor code. Update the distributor code field in the filtered data and set the flag to true. Now continue down your whole list of distributors until done.

After this has been done I would do a check for any records that have the flag field set to false as this would mean you don't have any code that matched it. The rest of the records should now be identified.

Hopefully I understood your problem correctly.

[djtreble]

Re: Sorting question

Problem with the above is before it matches up, I need it to remove the first 3 numbers (978), then take the remain 10 digits and find the distributor prefix within it and match the ISBN up with the correct distributor, then add that distributor next to the correct ISBN and print off in a report that shows all needed data in the above attached excel format and include the distributor for each purchase.

[Mike Wilson]

Re: Sorting question

Dylan,
my example works appropriately. The entry number is the ENTIRE 13 digit isbn number. Put in the entire isbn number and it will find the distributor (see attached pics).

The reason that you might have gotten a no match with some is because they are not in the example you first provided. For instance Alban, bookpoint and CUP are not. Try 9781854245748 though and it will return Lion because it is in there.

What you are asking for is NO problem, but you have to have ALL the distributors in a table to look them up.

Later:
OK, I know the problem. I changed the isbn_prefix to character and 06642 = Alban needs to be 6642, so it didn't find it.

[djtreble]

Re: Sorting question

ahhhh, sorry never knew i had to do that, thats great, thanks for that

How complicated will it be to put that into practice as above in a web solution?

[Mike Wilson]

Re: Sorting question

Dylan,
Your data is not complete and unless it is, it is hard to get you to your endpoint.

In the .xls zip desp 18.06.09, it lists the publisher as Hodder for many records. The publishers data list you posted has one entry for Hodder = 7195. There is no correlation between all the ISBN's in the desp 18.06.09 xls that has Hoder as the publisher and 7195. So it is tough to verify accuracy with the data files that you have posted with these descrepancies.

But, nonetheless, attached is an example. I took the desp 18.06.09 file and saved as a .dbf table named Orders. I made a script to empty the Publisher value from the table. I made a script to repopulate the publisher field BASED ON THE PUBLISHER TABLE THAT YOU ORIGINALLY PROVIDED. The results ended up different, from the values at the start and I am believeing it relates to the differences in the publisher data.

[djtreble]

Re: Sorting question

Thanks Mike, all your help is much appreciated.

The publisher that get's given to us from Amazon is irrelative. All that needs to be matched up is the ISBN to the list of distributors in the first excel file and then exported as an excel file again with the distributors listed next to their respective orders.
Can, instead of removing publishers and adding distributors in place, can we rather add another column called distributor and then populate that next to the publisher column?

Another thing I have notice which could be causing problems is the one distrubutor's prefix is 007 and not just 7....this can cause issues as I've found out by trying your first example. Is there a way of over-coming the automatice removal of the 0's before other numbers?

[Mike Wilson]

Re: Sorting question

OK,
I have amended the example to have it closer to what you describe. The orders table will hold the new orders that you wish to match with distributors, with the distributors present in the publishers table (these might want to be made more confluent in your permant app so not to create confusion). The product_sold() function uses the Orders table as a source, record-by-record uses the isbn of the Order's table finding a match with a distributor, and writes the order info and the distributor info to the products_sold table. This batch, I have thrown in the concept of being an "invoice" and generate an invoice number for the batch. The function then offers the user the choice to run the ps_export() function that exports that "invoice" as an excel file named by the "invoice number", or wait to run the export later.

Check it out and see what you think. Don't wory about 007. It was an issue in the first example, but the remaining examples returned the field to a numeric type. The field is a numeric field and 974007 resolves to 7 when extracted.