6 Replies Latest reply on May 9, 2012 7:11 AM by digital-carpentry

    Job Numbers in unrelated table


      In writing a new file I'm wishing to have serialized job numbers in a table that no user sees.

      Then when a user in a Layout hosted in Table B OR a Layout hosted in Table C needs a new job number they would retrieve the new number from the JobNumber table and also make sure the JobNumber is incremented for the next access.

      Any ideas how to best accomplish this?

      (probably elementry to most / many of you... but dang, I'm still on the bottom tier of what looks like a very steep learning curve)

        • 1. Re: Job Numbers in unrelated table

          It's not neccesary to put this in a separate table, just set a field in the table storing the jobs, but don't display it on the layouts for users to see.  You can set the field to Auto-Enter a serial number and it will take care of the incrementing for you automatically.

          • 2. Re: Job Numbers in unrelated table

            OK I just noticed you are wanting this to be based on jobs coming from 2 different tables, not sure what the purpose is in that, but if this is neccesary, I would recommend a job table in addition to Table A or B, and actually work from the Job Table, create a new record there (where the job number is set through auto-enter as I said) Then push it to the related record in Table A or Table B.  not knowing the function of Table A or Table B, I can't say for sure, but I would recommend working completely from layouts based in the Job Table and working with Table A and B through the relationship so that any new record would give you what you need, otherwise if you are in Table A and they create a new Record, you are going to have to force it to go back to the Job Table and create your Job record, which can be scripted, but you have to be careful not to let users create new records from the menu or keyboard commands without running the script as well.

            • 3. Re: Job Numbers in unrelated table

              That is a good idea...

              How does one "push" a field to a related record?


              I have three tables pertenant to this discussion;  JobNumbers, ServiceCalls and SalesCalls.

              Both ServiceCalls and SalesCalls need job numbers which can never be the same, as all jobs are costed through one JobCosting table.  All Labor and materials go to the appropriate job number.

              • 4. Re: Job Numbers in unrelated table

                If you base your layout on jobs and display the fields from the other table through the relationship, when you fill in the fields it will automatically create the related record and "push" the job ID for you.


                You may also want to validate that if there is a related record in "table A" there cannot be one in "table B" unless that is a valid option.



                Bill Pelfrey

                Digital Carpentry LLC



                "Let us build a foundation for your business."

                • 5. Re: Job Numbers in unrelated table

                  I really like the concept;  I could have a tab representing SalesCall entry and another tab representing ServiceCall entry. 

                  Should I build a portal to make those entries while in the "jobs" table/layout?

                  • 6. Re: Job Numbers in unrelated table

                    Yes, and put common information in the Jobs field (anyfield you would need for both) you can show those things at the top and then do tabs below for the more specific fields, you probably won't have to use a portal (a portal is for displaying multiple fields from another database) just display the fields within the tab.  This will give a seamless UI that works like a flat file, but the structure in the background will have the separate tables to break down the information.