5 Replies Latest reply on Mar 16, 2016 2:38 PM by earljr

    Associate expenses to user (related tables)

    marcopel

      Dear all.

      I'm try to make a simple database of the items we purchase in order to keep track of what each single user buys.

      I made a table called Item where I will store items names, price, quote, invoice etc.

      then I have a table with the list of users and their budgets.

      What I was trying to do is just that every time I create a new record in the item table I specify to which user the item is "associate" and I was expecting to have in the user table a list of the items and the summary of the total price.

      I made something that behave like this:

      Item table:

      itemIDItemNameItemPriceUserName
      ID01Some stuff10Mr.x
      ID02Other stuff15Mr.x

       

      That is what I see in the user table:

      UserIDUserNameItemNameItemPrice
      User01Mr.xI don't see anythingsame, is empty
      User02Mr.xAs aboveAs above

       

      I related them with a relation:

      Item table::UserIDfk >---User table:UserID

      Allowing creation of data from the item table to the user table

       

      The problem Is, indeed, when I create a new record in the item table I cannot associate it to a user that already exists, it creates a new user.

       

       

      Still I navigate in the ocean, I can probably give you more informations if is needed.

       

      What I would like to see instead is something like this:

       

      User table (or a layout):

       

      Mr.x

       

      Item Name
      Some stuff10
      Other stuff15
      Total25

       

       

      Is there anyone with a loot of patience that can help me please.

      Thanks a lot!

      but really , really, a lot.

       

      Marco.

        • 1. Re: Associate expenses to user (related tables)
          earljr

          Marco, 

          Easy

          You place a buton on the form to create the new record threw a script set. then get the user'name with a get(username) function.

           

           

          This should be translated to the user id that is in your user table. And you associate that new expense to his id.

           

          Need more details, ?

          Earl

          • 2. Re: Associate expenses to user (related tables)
            marcopel

            Hi Earl,

            thanks for replying and sorry to be back so late and..I think I did not follow your explanation ..

             

            Sorry about that but could I ask you to give me more details, indeed.

            Thank you

             

            • 3. Re: Associate expenses to user (related tables)
              earljr

              Marco ,

               

              How far do you want to go in this project ?

               

               

               

               

               

              For a simple explanation , you need to have the user table lead the expense table

               

              Your user’s primary key will be the table link the expense log table , with the help of the date table , it will be possible to sort your reports by month

               

               

               

               

               

              As you can see , I’ve started a basic layout of the tables needed.

               

               

               

               

               

              The important thing is  , your user table will be the creation link into the Expense log table

               

              The user , will be creating  logs associated with his ID…

               

               

               

              The only relation that has a record creation value set is the “User Ø Expense Log”

               

               

               

               

               

               

               

               

               

               

               

              Your users logon name should be set into the software of each user , so the system will respond automatically to it ..

               

               

                    

                         

               

               

               

               

               

               

               

               

               

               

               

              From this point on , you need to fill in te basic information

               

              Fill in the users table

               

              The months have been added to theh date date table

               

              Add the expense type either it be traveling in KM or miles (the conversion factor )

               

               

               

              And the final data will be added for each user in this interface …

               

               

               

              As soon as you type something into the description and other green items ,

               

              the item is added to the expense log , with the users ID ,the date, Time ,

               

               

               

              You chouse the expense type , and assigne a value to this expense..

               

               

               

              Have a look at the startup script…

               

               

               

               

               

               

               

               

               

               

               

               

               

              Here you go ,,it’s my demo ,,,

               

              Fool around it..

               

               

               

              if you do something extrodinary to this demo , , send me back a copy J

               

               

               

              Need any more information ,,,

               

               

               

              Glad to help

               

               

               

              Earl

              • 4. Re: Associate expenses to user (related tables)
                marcopel

                HI Earl, wow.

                That was very neat, thanks a lot it seems it works perfectly.

                 

                I will start to studying it and have a look in detail how did you do it.

                The only thing that I can already tell you now is that only one user (me) will be accessing the report.

                IN the working scenario someone will send me an email, I place the order and upload the expenses report specifying the user , so I can keep track of who spent what. So I guess it would work like it is without other changes.

                 

                I will also start to change it a bit for my needs and I will send you , the demo modified if would like.

                I hope I do not have to disturb you anymore and I think I will be able to figure out all from now on..

                I hope

                 

                Thanks again.