6 Replies Latest reply on Sep 9, 2009 3:36 PM by SSed

    auto fill-in sequential numbering over several fields

    SSed

      Title

      auto fill-in sequential numbering over several fields

      Post

      hey guys. I've looked through the forums and it has helped me quite a bit already but I think I need to ask something specific to get this thing finished. I am not a scripter, like many of the people asking questions on this board, so please explain it to me like I'm a three year old :p

       

      Ok, what I'm doing is duplicating records, adding unique serial number suffixes in order to track individual items that otherwise have the same data. There are four catagories that are pertinent to this question: RTP, AP, ED and Misc (business specific terms). I have "current" and "total" fields for each catagory. My goal is for the script to recognize the total number of an inventory catagory and then fill in the current field sequencially over duplicate records until it reaches the total number. After it reaches the total number then it moves on to the next inventory catagory, leaving the current field of the previous catagory empty afterwards. If this makes sense at all...

       

      My script is based of the knowledge base entry on automatic record duplication (here), and I'm trying to use the same logic for this part of the solution. This is how I've mangled it so far to fit my needs.

       

      Set Field [ Inventory v2::global_match_records; 1 ]

      Set Field [ Inventory v2::global_match_records_rtp; 1 ]

      Set Field [ Inventory v2::global_match_records_ap; 1 ]

      Set Field [ Inventory v2::global_match_records_ed; 1 ]

      Set Field [ Inventory v2::global_match_records_misc; 1 ]

      Set Field [ Inventory v2::global_match_duplicates; "1" ]

      Set Field [ Inventory v2::global_match_duplicates_rtp; "1" ]

      Set Field [ Inventory v2::global_match_duplicates_ap; "1" ]

      Set Field [ Inventory v2::global_match_duplicates_ed; "1" ]

      Set Field [ Inventory v2::global_match_duplicates_misc; "1" ]

       

      Loop

      If [ IsEmpty ( Inventory v2::duplicate_count) = 0 and Inventory v2::duplicate_count ≠ Inventory v2::global_match_duplicates ]
      Duplicate Record/Request

      Set Field [ Inventory v2::global_match_duplicates; Inventory v2::global_match_duplicates + 1 ]

      Set Field [ Inventory v2::serial_number_complex; Inventory v2::serial_number_complex + 1 ]

       

      //this is the section that needs work

      If [ IsEmpty ( Inventory v2::rtp_total ) = 0 and Inventory v2::rtp_total ≠ Inventory v2::global_match_duplicates_rtp ]
      Set Field [ Inventory v2::rtp_current; Inventory v2::rtp_current + 1 ]

      Else

      Set Field [ Inventory v2::global_match_records_rtp; 1 ]

      Set Field [ Inventory v2::global_match_records_rtp; Inventory v2::global_match_records_rtp + 1 ]

      End If

      //

       

      Else

      Set Field [ Inventory v2::global_match_records; 1 ]

      Set Field [ Inventory v2::global_match_records; Inventory v2::global_match_records + 1 ]

      If [ Get ( FoundCount ) = 0 ] Show All Records

      End If

      End If

      End Loop

       

      Thanks in advance for any kind of help you all can give me. I might have one more question if this one gets resolved...

        • 1. Re: auto fill-in sequential numbering over several fields
          philmodjunk
            

          You might want to consider a very different approach to managing inventory. You might want to use an inventory log table where each record records adding or removing items from inventory.

           

          This thread might give you an idea or two: http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=21633&query.id=70360#M21633

          • 2. Re: auto fill-in sequential numbering over several fields
            SSed
              

            hmmm sorry for the delay. Long weekend :)

             

            I'm not sure if this would be any more effective of a solution. That could be just an opinion of one ignorant of these things, as I'm merely an artist, not an accountant or bookkeeper. Our business is peculiar. We make editions of fine art where most of the data is the same for each individual piece except for several fields of unique data that are entered at a later date. Our inventory is never restocked, nor is it removed or deactivated from our records when sold. Tracking whole quantities isn't enough, we need to be able to pull up specific pieces within the edition (eg. 5 of 30 or AP 2 of 9). Individual pieces aren't sold in sequential order either, so we can't infer which pieces have been sold. Therefore, in my mind, duplicating records is the easiest and fastest way of creating our inventory system rather that creating a log file.

             

            I guess the best way to think about it is that we have thousands of individual, unique pieces of inventory in our catalog and they must be tracked accordingly. Yet most of the inventory is related to other pieces, so much of the information is duplicated. All of it could be entered by hand, but I'd rather not.

             

            so like I said, making a log file of inventory changes doesn't seem like it'd be any more effective for us. But I don't want to dismiss the suggestion either Phil, so if you can explain why and how it'd be a better solution, then I'm all for it. Otherwise, help with that scripting problem is all I need to complete this project. I'm so tired of bookkeeping guys, let me get back to work creating cool things! :D

             

            Let me know if you want to see any part of my solution to get a better grasp at what were doing, without my poor technical communication to hinder the process.

            -thanks 

            • 3. Re: auto fill-in sequential numbering over several fields
              philmodjunk
                

              Thanks for the additional information.

               

              You might want to check out the Replace Field Content tool. It has an option for loading a field with a serial number value and that might simplify your script considerably.

              • 4. Re: auto fill-in sequential numbering over several fields
                SSed
                   yeah I suppose that would work too. Is there a way to get it to stop once it reaches the total quantity for that item type and move to the next item type? I would have thought it'd work the same as the duplicate record method I'm using: create a global match field utilizing a boolean comparison for the two quantities.
                • 5. Re: auto fill-in sequential numbering over several fields
                  philmodjunk
                    

                  If you're working with more than one "group" of records at a time, you'd have to use a find or Go to related record to isolate the records you want to serialize in a found set of just those records.

                  • 6. Re: auto fill-in sequential numbering over several fields
                    SSed
                      

                    ok, that makes sense. I'll try that out.

                     

                    You're amazing PhilModJunk. Glad there's someone here who will put up with all the newbies :)