• (disco)

    When I opened this topic, I saw a post saying 'topic is now listed'. Then a second one appeared. Then it disappeared. Then the first one disappeared. Which was weird.

    But anyway…

    To me, the proper solution for this cost centre thing is to have a drop-down of existing values that users can add an entry to on demand. That would have avoided the import entirely too. But then hindsight is always 20-20.

  • (disco)

    I just saw 3 “this topic is now listed” posts by @PaulaBean, and one by @Remy. I hope it’s opened now... [image]

  • (disco) in reply to RaceProUK
    RaceProUK:
    When I opened this topic, I saw a post saying 'topic is now listed'. Then a second one appeared. Then it disappeared. Then the first one disappeared. Which was weird.

    Just making DiscoSure™...

    [image]

    S'pose I should go read the article now...

  • (disco) in reply to PJH
    PJH:
    S'pose I should go read the article now...
    It must be good; this topic was listed four times!

    It is actually quite good.

  • (disco)

    So... The WTF is that the OP doesn't know what a combobox is for?

  • (disco)

    Robert should have know better after the word 'centre de frais'. That's a book keeping term for merde géant.

  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    So... The WTF is that the OP doesn't know what a combobox is for?

    I think this didn't help:

    FTFA:
    It didn't take Robert long to create the new cost center table (**VARCHAR(100) seemed safe**),

    At a minimum, given the unknowns evident, I'd have used

    • a BIGINT as a FK into
    • a new table with AUTOINCREMENT and a VARCHAR initially

    That spreadsheet in the image looks as if it could be normalised as well so the actual form input could probably be presented as a series of comboboxes

    Regroupment :fa_caret_down: CDR Libellé :fa_caret_down:Famille :fa_caret_down:Libellé de coût :fa_caret_down: etc...

    With, initially, all but the first empty, with N+1 being populated once N had been filled in, with each dropdown having a manageable number of entries.

    This would then turn the new table into

    • AUTOINCREMENT
    • Series of FK's into the columns/comboboxes.

    If, of course, the closing statement didn't actually turn out to be

    FTFA:
    ...only a tiny handful of the 1800 cost centers had ever been used.
  • (disco) in reply to RaceProUK

    While that solution seems on first glance to be ok, I know from experience that it is NOT the case.

    My first data base system included just such a scheme for choosing storrage locations in a lab for chemicals etc.

    Imagine the fun when someone added '5 L bottle' (amongst others) to the locations list... (not to mention 'Toxic closet' as well as 'toxiccloset'..)

    Technically it was correct, but which bottle and on what shelf?? :smile:

    In my experience the only correct way is to add an administrator interface where the site administrators can add new 'types' to the system from which normal users can then pick.

  • (disco) in reply to Luhmann
    Original Article:
    "Well, you guys already use every available field in my database. This would require me to add a new field to the database and web forms."

    Pretty clear the client isn't the only WTF here. I mean, who designs a database without several empty fields just in case!? Hell, for large enterprise apps, I often create 2 or 3 entire empty tables for future growth!

  • (disco) in reply to Maciejasjmj

    Most combobox implementations will break down with this number of options. I recall a helper script for U.S. postcode comboboxes that filled the combobox's options table once the user had entered 3 (I think) characters, which returned few enough results to allow the combobox's programming to cope.

  • (disco) in reply to cdosrun1

    sp_help CompanyAppCoreTable

    [image]
  • (disco) in reply to Yazeran
    Yazeran:
    While that solution seems on first glance to be ok, I know from experience that it is NOT the case.

    My first data base system included just such a scheme for choosing storrage locations in a lab for chemicals etc.

    Imagine the fun when someone added '5 L bottle' (amongst others) to the locations list... (not to mention 'Toxic closet' as well as 'toxiccloset'..)

    Technically it was correct, but which bottle and on what shelf?? :smile:

    In my experience the only correct way is to add an administrator interface where the site administrators can add new 'types' to the system from which normal users can then pick.

    A fair point ;) But then there's also no point overcomplicating the matter either :stuck_out_tongue:
  • (disco) in reply to PJH
    PJH:
    If, of course, the closing statement didn't actually turn out to be

    FTFA: ...only a tiny handful of the 1800 cost centers had ever been used.

    But there's still a problem of "you made a typo and suddenly it's a whole different cost center". A combobox, or a series of ones, would at least prevent that.

    robbak:
    Most combobox implementations will break down with this number of options.

    What, a little below 2k? It's not a common use case, sure, but it's not something totally unheard of either.

    In any case, if not a combobox, then a list with a filter. You start typing, and the list gets filtered down to cost centers that match the string.

    In any way, I really don't see how the request would be more unreasonable than the developer throwing their hands up and basically saying "there's no way we can give users that many choices". And then (as I understand it) opting for a string instead of a proper ID to a cost_centers table, with no verification that the cost center actually exists.

  • (disco)

    Un petit d'un petit S'étonne aux Halles

  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    But there's still a problem of "you made a typo and suddenly it's a whole different cost center". A combobox, or a series of ones, would at least prevent that.

    Autocomplete with the already-used cost centers; a user can insist on adding new ones, but at least you can make it easy for them to use an existing one.

  • (disco) in reply to dkf
    dkf:
    Autocomplete with the already-used cost centers

    IMO that's just asking for problems when someone does indeed make a typo. They have a list of all possible cost centers, and assuming no exceptions, it only seems reasonable to limit the choice.

  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    IMO that's just asking for problems when someone *does* indeed make a typo. They have a list of all possible cost centers, and assuming no exceptions, it only seems reasonable to limit the choice.

    But you do not want a dropdown for 1800 choices. Dropdowns are best when you're picking between, say, 10–15 options. (They're not great even for days of the month, and that's a common use.) For many hundreds, a search-based interaction is right.

  • (disco) in reply to dkf
    dkf:
    But you do _not_ want a dropdown for 1800 choices. Dropdowns are best when you're picking between, say, 10–15 options. (They're not great even for days of the month, and that's a common use.) For many hundreds, a search-based interaction is right.
    The software I work on has searchable dropdowns; would they be a reasonable compromise? They work well enough for a couple hundred ledgers.
  • (disco) in reply to RaceProUK

    Yeah, that's kind of what I'm talking about. You type in a few letters of the name to narrow down the list, then pick an actual cost center from the list.

    I don't know what it's called, but I've seen this kind of control quite a few times.

  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    I don't know what it's called, but I've seen this kind of control quite a few times.
    Combobox, I think, though the definition may not explicitly include the searching.
  • (disco)

    The correct control in this case is a plain old text box with autocomplete, and possibly a button to pop up a search interface. Do not allow users to add brand new entries. If the text in the text box doesn't match exactly one of the values in the database, reject it.

  • (disco)

    Displaying the info is not the problem here, TRWTF is the developer thinking it was impossible to add the data to his schema because magic.

  • (disco) in reply to anotherusername

    Exactly, I've done this thing several times and if the data is too large, add an AJAX request. Hell, it took me 5 seconds to find this:

    http://jqueryui.com/autocomplete/

    Come on! OP you're TRWTF!

  • (disco) in reply to dstopia
    dstopia:
    TRWTF is the developer thinking it was impossible to add the data to his schema because magic.

    You don't read too well. He'd run out of spare columns. The magic is required to somehow add new columns

  • (disco) in reply to anotherusername
    anotherusername:
    a plain old text box with autocomplete,
    anotherusername:
    Do not allow users to add brand new entries

    Those send somewhat contradictory messages. I'd put a filtered list below the textbox because of that.

    But the OP is TRWTF in any case.

  • (disco) in reply to Maciejasjmj

    Not really. And the autocomplete can do just that: float a list of possible entries below the textbox as you type. If you leave the textbox without selecting an option, the highlighted match (best, by default) should be selected so the textbox will always contain a valid code.

  • (disco) in reply to Jaloopa
    Jaloopa:
    You don't read too well. He'd run out of spare columns. The magic is required to somehow add new columns

    Imagine the possibilities if he had read about the ALTER incantation.

  • (disco) in reply to Eldelshell

    To avoid having to alter the hundreds of stored procedures and views in my employer's codebase which select *, I have just added a lookup table instead of adding a column to a widely used table. It's a many too many style table, but with a UNIQUE constraint on the foreign key to the troublesome table

  • (disco) in reply to Jaloopa
    Jaloopa:
    He'd run out of spare columns. 
    CREATE TABLE USERS (
...
justincase1 VARCHAR2(2000),
justincase2 VARCHAR2(2000),
justincase3 VARCHAR2(2000)
);
  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    CREATE TABLE USERS (
...
justincase1 VARCHAR2(2000),
justincase2 VARCHAR2(2000),
justincase3 VARCHAR2(2000)
);

    Better make those BLOBs or similar, otherwise you're going to hit some row-capacity constraints sooner rather than later. Unless you use postgres it seems:

  • (disco) in reply to Eldelshell

    +1

    another implementation

    @Maciejasjmj HTML5 calls it "Datalist"

  • (disco) in reply to PJH

    For SQL Server, just do this:

    CREATE TABLE USERS (
...
justincase1 NVARCHAR(MAX),
justincase2 NVARCHAR(MAX),
justincase3 NVARCHAR(MAX)
);

    No worries about row size limits. But indexing will be a mare.

    Edit: @Jaloopa has a point there

  • (disco) in reply to RaceProUK
    RaceProUK:

    For SQL Server, just do this:

    CREATE TABLE USERS (
...
justincase1 NVARCHAR(MAX),
justincase2 NVARCHAR(MAX),
justincase3 NVARCHAR(MAX)
);

    No worries about row size limits. But indexing will be a mare.

    FTFY. Unicode is important

    edit: thanks, @PJH

  • (disco) in reply to Jaloopa
    [image]

    [quote="Jaloopa, post:39, topic:37086"] [quote="RaceProUK, post:38, topic:37086, full:true"]

    For SQL Server, just do this:

    CREATE TABLE USERS (
...
justincase1 NVARCHAR(MAX),
justincase2 NVARCHAR(MAX),
justincase3 NVARCHAR(MAX)
);

    No worries about row size limits. But indexing will be a mare. [/quote]

    FTFY. Unicode is important [/quote]

    A blank line after the [quote="RaceProUK, post:38, topic:37086, full:true"] will fix your formatting issues.

    INB4 why doesn't pressing backspace once delete two characters anymore....?

  • (disco) in reply to Jarry

    Don't get me started on that one

    http://caniuse.com/#feat=datalist

  • (disco) in reply to PJH

    why didn't you DO YOUR JOB and fix it for me? ;)

  • (disco) in reply to Eldelshell

    yes, i know i know. i don't use that tag, but it gives a name to something that didn't had one.

  • (disco) in reply to Jaloopa
    Jaloopa:
    why didn't you DO YOUR JOB and fix it for me?

    I thought educating the masses (rather than dealing with your ignorance) was a better use of the time I spend Doing My Job™ on here.

    Your welcome.

    ... and his welcome, and her welcome...

  • (disco)
    ... we'd like for them to be able to pick the one they need from a list.

    I have a (non-gag) reflex for this:

    How long is the list?

    "1800 items" is not a dropdown. For that matter, anything over about 20 items is not a dropdown. > 20 needs something special like the combobox or maybe one of those fancy ajax-matching dropdowns which tells you "these are the first 10 items matching your input, there are 1000 more".

    Assuming we even want to implement this increasingly-complicated solution, one still needs to load a spreadsheet which, "cleaned" as it might be, is unlikely to be development-clean.

    Furthermore, the estimate for the feature is based on the information I have. If the information is incomplete, the estimate is 6 months, which restarts every day until I have all the information I need to actually make an estimate.

    #thingsiwanttosaytoclients

Leave a comment on “A Petite Change Request”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article