3 Replies Latest reply on Nov 10, 2010 5:01 PM by david583

    X Access user needs help with a  multiKey serial number

    ToddLegere

      Title

      X Access user needs help with a  multiKey serial number

      Post

      Need some help  converting  a work order data base from Access to FM.

      Our work order numbers are department specific.   Electrical as an example would use  E0001….  Where plumbing would use P0001…

      With Access my table uses 3 fields to accomplish this.

      1-    Type – This is a stored value from a look up table, this is a letter that defines the type of work order .  E= Electrical,  P= Plumbing, C= Controls…

      2-    WOSeqNum – This field keeps track of previously used numbers.

      3-    WoNum-  this is the combined number IE .. E0001

      I would like to point out that I require sequential numbering.  Each division uses  the same numbers  IE.  Electrical will be  E0001,E0002, as will Controls be C0001, C0002…

      The code I used to accomplish this in Access was.

      WOSeqNum = Nz(DMax("WOSeqNum", "TblPO_numbers", "Type = '" & Me.Type & "'"), 0) + 1
WoNum = [Type] & "" & [WOSeqNum]

      What do I need to research to do this in FM?  I’ve been reading up on multikey but I done seam to be getting anywhere. 

      Any help would be appreciated.

      Todd

        • 1. Re: X Access user needs help with a  multiKey serial number
          david583

          A text field that combines your prefix letter with the serial number can be done easily, it would just concate with an auto-enter serial number.

          However, to sequenially number by each individual prefix cannot be done with auto enter unless you seperated 'E', 'P' and 'C' into different tables. That would then require only one field as the prefix can be set at the auto-enter stage.

          You could then create another table that draws data from all three tables, this way each record will be shown with their own serial number and prefix.

          Or someone else may now a better way...............

          Hope this Helps

          David

          • 2. Re: X Access user needs help with a  multiKey serial number
            ToddLegere

            Thanks David, 

            If I understand you correctly, Ill duplicate my main work order table several times. Rename and set up an auto number field so that each table is unique to each department .   When a user logs into the database,  they will choose what table to operate from ( electrical, plumbing, controls...).  Then I guess as each new record is created there will be a script that will then copy this data into the main table.   does this sound right?

            • 3. Re: X Access user needs help with a  multiKey serial number
              david583

              Yes that's what I had in mind. It does seem a bit cumbersome, but it does work.

              On mine, at the end of the edit they click a commit button that copies the data across to the main view table but each department remains in seperate tables. All my reporting etc is done from the main view table, so the boss only sees the combined table, section heads can see only their data on their own table. This way I don't need filtered portals to seperate departments when the user is not interested in the overall view.

              It does involve 'doubling-up' your data, so there a major storage space drawbacks!

              There is most likely a better way to do this (I am also only an amatuer) and hopefully one of the Pro's (that are so incredibly helpful and generous on this forum, couldn't have got this far without them) could tell you where I'm going wrong.......

              David