3 Replies Latest reply on Jan 16, 2012 3:44 PM by philmodjunk

    IF(AssetsItem starts with LITK add 21 days to the check out date to give me a due date)

    MStephens

      Title

      IF(AssetsItem starts with LITK add 21 days to the check out date to give me a due date)

      Post

      Hello

      Still a newbie, so go slow

      Used starter solution Lending Library

      renamed field 'item' to AssetsItem, same table

      Starter solution none of the Large tables are related; I related History and Assets using Assests Asset ID table, AssetsAsset ID field.

      Here is what I have done;

      All AssetsItem numbers begin with letters, an hyphen, followed by numbers; e.g OT-???? AT-???? SPED-???? LITK-????.  I started with a simple calculation for the due date.  check out date + 45, works great

      Here is my NEW dilema:

      Powers that be would like the 45 days of use to continue for anything AT or OT, BUT anything SPED they want out for ONLY 14 days and anything LITK they want out for ONLY 21 days.

      What I thought I could do and didn't work

      Specify Calculation shows to Evaluate this calculation from the context of the History Contact ID (I am assuming it is because that is where my due date is at at the moment

      I am trying:

      If ( Assets::AssetsItem = "LITK" ; History::Check Out Date + 21)

      If ( Assets::AssetsItem = "SPED" ; History::Check Out Date + 14)

      If (Assets::AssetsItem = anything else; History::Check Out Date +45) (havent been able to figure out the anything else because 

      But the first line of the above has 'AssetsItem' highlighted with a dialog box stating that 'This field comes from an unrelated table.  Only global fields can be referenced in unrelated tables.

       

      Relationship_graph_for_Lending_Library.jpg

        • 1. Re: IF(AssetsItem starts with LITK add 21 days to the check out date to give me a due date)
          philmodjunk

          none of the Large tables are related

          Actually they are, but under different names. The boxes found in Manage | Database | Relationships are not actual tables, they are table occurrences. Each "box" refers to a specific table found on the tables tab of manage database. If you hover the mouse over the upper left corner of these boxes, the data source table--the real table from the tables tab will appear and you'll find that there are fewer tables in this starter solution than you have boxes here because the same table is part of more than one relationship, but using different table occurrence boxes.

          Specify Calculation shows to Evaluate this calculation from the context of the History Contact ID (I am assuming it is because that is where my due date is at at the moment

          Unless a contact is limited to checking out only one item and cannot check out any other items until it is returned, the due date should not be defined in the contact table. Instead, it should be defined in the History table--which would be the purpose of the Due Date date field found in this table.

          Case ( Left ( Assets::AssetsItem ; 3 )  = "LITK" ; 21 ;
                      Left ( Assets::AssetsItem ; 4 ) = "SPED" ; 14 ;
                      45 ) + Get ( CurrentDate )

          Would compute the due date. This could be an auto-entered date calculation defined for Check In Date in the History table. To add an auto-enter calculation, find Check in Date in Manage | Database | Fields and double click it. Then click the auto-enter tab. Then click the calculation option. This date will be computed at the time a new History record is created--which records the date an asset was checked out and will then compute its due date.

          But I think you need to look over an original copy of the asset lending library solution and figure out how the table occurrences work first.

          • 2. Re: IF(AssetsItem starts with LITK add 21 days to the check out date to give me a due date)
            MStephens

            Newbie asking stupid questions but:

            (Left, is that meaning you want to start to the left and count over 3?  If not counting what is the number 3 & 4 used for?

            do I not need to show the 21 added to check out date?

            The 45)+Get (Currentate) will that take the OT and the AT into account and add 45 days to the return date?

             

            no, the staff can check out multiple items at once.

            Thanks for your help, shall give it a shot in the morning when back at work.

            • 3. Re: IF(AssetsItem starts with LITK add 21 days to the check out date to give me a due date)
              philmodjunk

              Case evaluates to select the specified value (or calculation) paired with the first boolean expression that evaluates as "True". In this example, case will return one of three values 21, 14 or 45. Whichever of those three values that is returned will be added to the current date--which should be the same as your check out date--so you can use that instead of Get ( currentdate ) and just make this a regular calculation field instead of auto-entered if you want.