attribute details : type :where in the access database

[feemish]

Hi,

v7.0.6

All my products come in many different sizes.

I use a componant and an attribute and then a choice.

On the attribute details, I have the type set to 'text' on every item.

I actually need this set to 'integer' on every item.

Of course, I dont want to go through every item manually and change them one at a time.

Does anyone know on which table in the database the attribute details:type is ? and what its called on the database? ... so I can (hopefully) change them all in one go?

Cheers.

[NormanRouxel]

ProductProperties where the nValue3 is 1 for Text and 2 for Integer.

Use extreme caution, backup first and test thourougly afterwards. If you use Duplicates make sure they are done too.

[feemish]

Hey !

Thanks Norman.... You're a star.

[feemish]

I'm trying to get my search properties to appear in the right order on the search page.

I made the change en masse from text to integer, and the change worked, however the integer option wouldnt upload as I also have text in the choice.
I use 2mm, 4mm, 6mm etc.

So i changed it back to text.

and again my search properties wont list in the correct order.

The problem can be seen here.
http://www.arka-shop.co.uk/acatalog/search.html

Its not an option to not have the 'mm' as the items I sell are sometimes measured using 'Gauge' (imperial) as a size, and without 'mm' or 'gauge' the customers would be confused.

Does anyone know of a way to get my search options to list in the correct order?

Best Regards,
Mark

[malbro]

Does anyone know of a way to get my search options to list in the correct order?

Best Regards,
Mark

Put a leading 0 for the number less than ten then they will sort correctly, its all down to text sorting on the first digit then if there are more than one with the same digit sorting on the second digit etc. Make all the numbers 1-9 as 01 to 09 and it will work up to 99, if more than 99 then use three digits.

[feemish]

Hi Mal,

Thanks for the reply.

Yes, I can see that would work, though it might look a bit odd, I could live with that.

The problem is that I have hundreds of choices that have been entered already. Going and changing every '2mm' choice to '02mm' would take all day... and of course I'd need to do it for all sizes up to 9mm.

Can anyone think of a quick way to do this... maybe in the access database?

Or else is there a way to 'force' the choices to list in a particular order?

Cheers,
Mark

[malbro]

Can anyone think of a quick way to do this... maybe in the access database?

Or else is there a way to 'force' the choices to list in a particular order?

Cheers,
Mark

If you have a copy of Access then it is fairly easy to do a global replace, one way would be to just run down the column in Access and change as necessary. That would be fairly quick.

Backup all files before modifying the Access database

[feemish]

Would that be in the table : Product Properties ?

and the column sString1 ?

replacing all 2mm with 02mm etc ?

I could give that a go..

[malbro]

Would that be in the table : Product Properties ?

and the column sString1 ?

replacing all 2mm with 02mm etc ?

I could give that a go..

I am not an expert on V7 but in V8 it would be sString3 which is the 'HTML for the name', sString1 is the 'Choice Name'.

NOTE: ProductProperties holds a lot of different data depending on how the product is setup but it should be fairly obvious which ones you need to alter

[NormanRouxel]

Instead of 01, 02, etc, try <space>1, <space>2, etc. Where <space> is the spacebar character. That may have the same effect but be less distracting.

[malbro]

Instead of 01, 02, etc, try <space>1, <space>2, etc. Where <space> is the spacebar character. That may have the same effect but be less distracting.

Wasn't sure if this would work, whereas I know the 0 does, tried it in Excel and the sort there does work with space so it looks like this is a better solution.

[feemish]

Hey Mal, thanks for the advice !

and Norman, thanks, thats an awesome solution I had no idea would work.
I'll give it a try later and report how it goes.

Cheers

[malbro]

Hey Mal, thanks for the advice !

and Norman, thanks, thats an awesome solution I had no idea would work.
I'll give it a try later and report how it goes.

Cheers

Here the simplified techie explanation.
Computers do not understand letters just numbers, all the characters which we call alphanumeric (0 to 9, A to Z, a to z) are seen by the computer as a number in the range 32 to 127, there are other characters as well but they are not usually involved in sorting. When a computer sorts it looks at the value of each character as a number and sorts with lowest value first. Space is 32 in this system (ASCII) and '0' is 48, '1' is 49 etc so as far as the computer is concerned any item beginning with '1' comes before anything beginning with '2', irrespective of the number of letters in each item so it sorts
0
01
02
1
10
11...
19
199
2
20
etc.

The computer needs to have all the items of the same length to get an alphanumeric sort to work correctly which is what is being suggested. As space (32) is lower than '0' (48) it also works but has the advantage of looking better to humans, the computer doesn't care. However some software treats spaces as special characters and removes them from the item before sorting the list of items so using space doesn't always work.

[feemish]

Hi,

Well after all that, I had a go at the changes mentioned.

First, the value to change seems to be sString1. ( not sString3)
sString1 is the choice name, and this is what seems to be responsible for the order of the choice listing.

Second, adding a <space> didnt work !
So thats a shame, because that would be the neatest solution.

At least I couldnt make it work for me... I'm pretty sure I was doing it correctly. If anyone can make a <space> come first in a listing I'd like to know!

Adding a zero, of course does work, but doesnt look as good.

I tried underscore (_) and that listed at the end..
Minus (-) worked, and came first but also looks a bit confusing.

So I'm undecided what to do at the moment...

Thanks for your help though guys, all good ideas.