Announcement

Collapse

New Sub-Forum

In an effort to help make sure there are appropriate categories for topics of discussion that are happening, there is now a sub-forum for databases and database programming under Special Interest groups. Please direct questions, etc., about this topic to that sub-forum moving forward. Thank you.
See more
See less

Our coding solutions strategy predicated on experience and knowledge

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Our coding solutions strategy predicated on experience and knowledge

    I have always thought that the code solutions we settle on are usually predicated on several factors and mainly on our experience and knowledge. I often look back at code that i have generated and have thoughts such as :
    • OK I learned to achieve that and it works .
    • It works but it is timely to maintain , update.
    • is there a better or neater way to do this.
    • Would i be embarrassed to share my solution code ?

    Taking these thoughts as a basis of exploration, i would like to explore a program that I use and give this outline of the project.

    A databases of "parts" "valves" or "bolts"
    A database of Bill or materials.
    Each Bill of material can contain Parts Valves , Bolts and manages the total costs weight etc for these "parts" "valves" or "bolts"
    Furthermore a Bill of materials can contain another Bill of material in a hierarchical manner. ( Nested BOM's)

    Without yet revealing what strategy I took 10 years ago to code a solution to this I would like to know high level "possible strategy solutions from forum users to these objectives.

    To give you an idea a simple BOM with nested BOM's might look like this tree view screen shot..

    I am interested in thoughts on what strategy forum users would consider and not a fully detailed solution.





    Click image for larger version

Name:	Bill of Materials Treeview .JPG
Views:	126
Size:	122.1 KB
ID:	797068
    “Oh wad some power the giftie gie us To see oursel's as others see us! It wad frae monie a blunder free us, And foolish notion”

    Robert Burns (1759-96)

  • #2
    1. Do you need the BOM to contain routing or just components?

    2. Am I eligible if I did this commercially before? (OK, so that was back in the 80s)

    3. Do you have access to an "off the shelf" DB product which natively supports hierarchical ( eg IMS)? Or only relational (eg just about everything today)?

    And the big one..

    WHAT do you need to be able to do? I *ASSUME* you will want to store factioids about each item and one output will be a :"list of all raw components I need to create <X> finished goods." If so, are subassenblies stocked or is everything MTO? And if you need routing, do you need to produce something which will enable something like critical path management?

    Or are we just starting with "blank page, today's technology, no budget constraints, with the goal of 'kickjng around some ideas'?"
    Michael Mattias
    Tal Systems Inc. (retired)
    Racine WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      • OK I learned to achieve that and it works .
      • It works but it is timely to maintain , update.
      • is there a better or neater way to do this.
      • Would i be embarrassed to share my solution code ?

      Do you create your own indexes or do you use something extremely simple like SQLite?
      If you are not familar with SQLite it is definitely worth it in my opinion since relational tables are so easy.
      Code:
      'Unlimited clients with unlimited memos using relational key "num".
      'Supports binding, transaction processing and optional client/server.
      'More features will be added upon request.
      
      #DIM ALL
      #INCLUDE "sqlitening.inc"
      
      FUNCTION PBMAIN () AS LONG
      
       LOCAL sNum,sName,sIpAddress,sPortNumber,sResult(),sDelimiter AS STRING
      
       sDelimiter = "|"
       sIpAddress  = ""
       sPortNumber = ""
       IF LEN(sIpAddress) THEN slConnect sIpAddress,VAL(sPortNumber)
      
       slopen "test.db3","C"
       'slexe "drop table if exists Client":slexe "drop table if exists Memo"
      
       slexe "create table if not exists Client(num integer primary key,name text)"
       slexe "create table if not exists Memo(num integer,memo text)"
       slexe "create index if not exists MemoIdx on Memo(num)" 'suggested
       DO
        sNum = FORMAT$(RND(1,3)) 'remove if you like
        sNum = INPUTBOX$("Client number","0 = Cancel",sNum)
        IF VAL(sNum)<1 THEN EXIT DO
      
        slexe "Begin Immediate"
         sName= "Client" + sNum
         InsertUniqueClient sNum,sName
         InsertMemo  sNum, "memo at " + TIME$
        slexe "End"
      
        slSelAry "select name,memo from Client,Memo where Client.num = Memo.num " +_
                 "order by Client.num",sResult(),USING$("Q#c",ASC(sDelimiter))
        ? JOIN$(sResult(),$CR),,"Please click Ok"
       LOOP
       BEEP
       IF LEN(sIpAddress) THEN slDisconnect 'optional, cleaner using server
      
      END FUNCTION
      
      FUNCTION InsertUniqueClient(sNum AS STRING,sName AS STRING) AS LONG
       slexebind "insert or replace into Client values(?,?)",slBuildBindDat(sNum,"T") +_
                                                             slBuildBindDat(sName,"T")
      END FUNCTION
      
      FUNCTION InsertMemo(sNum AS STRING,sMemo AS STRING) AS LONG
       slexebind "insert into Memo values(?,?)",slBuildBindDat(sNum,"T") +_
                                                slBuildBindDat(sMemo,"T")
      END FUNCTION
      How long is an idea? Write it down.

      Comment


      • #4
        Click image for larger version

Name:	memo.png
Views:	76
Size:	17.9 KB
ID:	797091


        Code:
        #INCLUDE "sqlitening.inc"
        MACRO bindS(parm)= slBuildBindDat(parm,"T") 'bind as string
        
        FUNCTION PBMAIN () AS LONG
        
         LOCAL sClientkey,sClientName,sMemo,sql,sResult() AS STRING,x AS LONG
        
         KILL   "test.db3"
         slopen "test.db3","C"
        
         slexe "create table if not exists ClientTable(clientkey text,clientname text)"
         slexe "create unique index if not exists idx1 on ClientTable(clientkey)
        
         slexe "create table if not exists MemoTable(memokey text, memo)"
         slexe "create index if not exists idx2 on MemoTable(memokey)"
        
         FOR x = 1 TO 3 'insert or replace client if found and insert 3 memos
          sClientKey  = "KLUME"
          sClientName = "Heidi Klume"
          sMemo = "memo"+STR$(x)
          slexebind "insert or replace into ClientTable values(?,?)",bindS(sClientKey) + bindS(sClientName)
          slexebind "insert into MemoTable values(?,?)",bindS(sClientKey) + bindS(sMemo)
         NEXT
        
         sql =  "select memo from memotable where memokey="+ WRAP$(sClientKey,$SQ,$SQ)
         slSelAry sql,sResult(),"Q9c"
         ? CHR$("KEY=",sClientKey,"  ","CLIENT=",sClientName,$CR,JOIN$(sResult(),$CR)),,sql
        
        END FUNCTION
        How long is an idea? Write it down.

        Comment


        • #5
          Thank you Michael for your reply.

          1. Do you need the BOM to contain routing or just components?
          By routing I suppose you are referring to activities at work stations etc if so that is not required. Just components.


          2. Am I eligible if I did this commercially before? (OK, so that was back in the 80s)
          You are eligible as you have helped and advised on so many occasions in the past. I am as you suggest in your last sentence of your reply just kicking around ideas, to see if I can improve on what I have created.

          3. Do you have access to an "off the shelf" DB product which natively supports hierarchical ( eg IMS)? Or only relational (eg just about everything today)?
          Over the years I have tried several approaches using ODBC with Oracle and MySql. I have never used IMS and even had to look it up today. Having do so I can see and understand fully the dependency upon, data at the higher level of the hierarchy as a I had to write a solution to that particular issue when it comes to nested totals. In truth it id probably in this area that I have asked myself the questions such as, is there a better or neater way to do this? etc as mentioned at the start of this thread.



          WHAT do you need to be able to do? I *ASSUME* you will want to store factioids about each item and one output will be a :"list of all raw components I need to create <X> finished goods." If so, are assemblies stocked or is everything MTO? And if you need routing, do you need to produce something which will enable something like critical path management?
          Factoids yes dry and operating weight ,cost, paint area etc yes critical path management no.


          I notice that Mike Dotty has also replied so I do intend to post some simplified code , warts and all to show how I went about this as soon as I can after reading Mike's reply.

          Hopefully once I have done that folks can comment and advise.


          “Oh wad some power the giftie gie us To see oursel's as others see us! It wad frae monie a blunder free us, And foolish notion”

          Robert Burns (1759-96)

          Comment


          • #6
            Click image for larger version

Name:	variablelength.png
Views:	71
Size:	11.8 KB
ID:	797101

            Easier with one table using key + variable length data
            Code:
            #INCLUDE "sqlitening.inc"
            
            FUNCTION PBMAIN () AS LONG 'variablelength.bas
             slopen "test.db3","C"
             slexe "create table if not exists table1(MyKey text unique, MyData text)"
             upsert "key1" ,"Hello, "
             upsert "key1" ,"world!"
             showresults "select * from table1"
            END FUNCTION
            
            SUB ShowResults(sql AS STRING)
             DIM sResult() AS STRING
             slSelAry sql,sResult(),"Q9"
             ? JOIN$(sResult(),$CR),,"Done"
            END SUB
            
            FUNCTION upsert(BYVAL sKey AS STRING,BYVAL sData AS STRING) AS LONG
             slSel "select * From table1 Where MyKey ='" + sKey + "' limit 1"
             IF slGetRow THEN
              sData = slfn("MyData") + sData
              slExeBind "Update table1 Set MyData = ? Where MyKey =" + WRAP$(sKey,$SQ,$SQ),slBuildBindDat(sData,"T")
             ELSE
              slexeBind "Insert into table1 values(?,?)",slBuildBindDat(sKey,"T") + slBuildBindDat(sData,"T")
             END IF
            END FUNCTION
            How long is an idea? Write it down.

            Comment


            • #7
              Oops, forgot to ask...

              Are subassemblies and/or component parts sold separately as spare parts? Or do you (as do many firms) assign a separate part number for spare parts sales?
              Michael Mattias
              Tal Systems Inc. (retired)
              Racine WI USA
              [email protected]
              http://www.talsystems.com

              Comment


              • #8
                Mike & Michael
                Just a quick note to say that I have had to attend to some other urgent matters at the moment. However I have decided to post a working example of the key functions of the code such this will in some ways better explain my initial questions, and help you to see the possibilities of other solutions. Hopefully i'll get something posted here soon, and thank you both for the interest in this somewhat unusual request from me.
                “Oh wad some power the giftie gie us To see oursel's as others see us! It wad frae monie a blunder free us, And foolish notion”

                Robert Burns (1759-96)

                Comment

                Working...
                X