1 Reply Latest reply on Jul 19, 2015 7:36 AM by SteveMartino

    Tricky Inventory Number System PART TWO

    AndrewJudd

      Title

      Tricky Inventory Number System PART TWO

      Post

      HI all, i've posted about the above before but that thread has got so long and complicated (and this new post is about an earlier issue that I thought best to starting a new one)...

      I am building an inventory system that shoukd match full name and date. Therefor add 1 to the number system when these two fields match. i have set up a related table (also based on artwork - which is where the entries are made).

      I then have an inventory number with the following calculation.
      Left(SURNAME;5) & DATE & "_" & Right ("00" & Inventory Number ;3)

      The field "inventory number" does the following:
      If ( IsEmpty (ARTWORK_SAME_ARTIST::s_Max_Inventory_Nr) ; 1 ; ARTWORK_SAME_ARTIST::s_Max_Inventory_Nr +1 )

      The summary field "s_Max_Inventory_Nr" is the maxiumum of inventory number.

      I am getting some strange results.

      Basically when you enter a new name the following happens - when the artist name is the same the number stays on 001.

      When you enter a date it jumps up to 29 for some reason. Even same artist name and same date stays on 29.

      I know the ideal is to have artists in a related table and use their key but this database is not set up like that. Just want to keep is fairly simple but make it work.

      Thank you!!

       

       

      Screen_Shot_2015-07-19_at_11.43.25.png

        • 1. Re: Tricky Inventory Number System PART TWO
          SteveMartino

          Trouble:

          Just want to keep is fairly simple but make it work.

          If you know this....

          I know the ideal is to have artists in a related table and use their key

          Then you should change and fix it once and for all.  In my very short time with FM (little over 3 years) the axiom "If you have to do something very complicated to solve a simple problem, then you most likely have a problem with your structure" really holds true here.

          What happens down the road when you have to change something that you really want/need, and your whole solution to the complicated number scheme takes a crap....well you're going to have to rebuild.

          Do it now, or do it later.  Later is worse.  Ask anyone who's built at least one DB.  Unless you like the learning exercise of rebuilding.

          Here's an excellent paper for 'novices'.  Funny thing was I read it when I first started a DB as a novice.  Didn't fully understand, or appreciate all the advice.  Just read it again last night, wish I would've re-read it sooner.

          Just trying to offer some friendly advice.