1

Help with Scripting

Hi, 

I am trying to add logic to a function that I created that will flip the item status from listed to sold, donated, returned, etc. I would like to put in a safety piece that will prevent this function from updating the status of the items if they are found in future transactions. I have a table called "Item History" which references the item from my inventory itself and the transaction date. This table is where there could be multiple transactions in the event of a return, etc. In my transactions, I have a button to perform the update (see photos) but I am not sure how to write the bit that should look at the "Item History" table for all records that contain the item(s) in the transaction and determine if the current transaction date is the largest value compared to all the dates for that item in the "Item History" table. If it is, it should update, if it is not, it should not do anything or prompt the user that it could not update due to a future transaction existing with the item(s). 

 

I greatly appreciate any help you are able to provide!


Cheers!

 

Item History Table

Transaction Record showing "Status" in the transaction Items

Current logic is performed when "Update Record" is clicked.

3 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Hi there -

    One option is to use the order by command. Since 'Item History' is linked to Transaction you can do something like: 

    let x := last('Item History' order by Date);
let xIHDate := x.Date

    On line 1, the code inside the parentheses tells Ninox to find all records in 'Item History' that are linked to the record you are viewing in Transaction and then ordered the results by ascending Date. Then the last command says find the last record, which should be the record with the most recent date.

    Then in line 2 we create another variable that finds the value that is stored in the Date field.

    Once this all makes sense you can make it one line.

    Now you can then create an if statement that compares the Date field in Transaction to the xIHDate variable. 

    if Date > xIHDate then...

    On another note, instead of doing so many nested if statements, may I suggest you look into the switch command. Since your Type field is a choice field you can use the switch command instead of nested ifs. Assuming that Sales is choice 1 and so on:
      

    switch Type do
case 1:
'Transaction Items'.Item.(Status := 4)
case 2:
'Transaction Items'.Item.(Status := 5)
case 3:
'Transaction Items'.Item.(Status := 6)
case 4:
'Transaction Items'.Item.(Status := 8)
case 5:
'Transaction Items'.Item.(Status := 1)
end

    So we have taken 19 lines and compressed them into 12.

    I hope this helps and let us know how it goes.

    • strawberry_ocean
    • 2 yrs ago
    • Reported - view

    Thank you so much  Fred ! 

    Unfortunately, it didn't work as expected, I think it is because I actually need it to look at all the records in the Item History table, not just the ones linked to the current transaction, that contain the same item like the one in the current transaction record. The items themselves are entered into their own table called "Inventory" for tracking their status and purchase cost.

    Is there a way to have Ninox look at all item history records that match the same item as the current transaction record instead?

    The Item History table is used to track each time that item is added to a transaction and what the item sale or refund amount is for that transaction, which is why it is important the update button does not update the status if accidentally pressed when reviewing an old transaction. 

    I did update the "if" statements to your suggestion! Thank you for that, I am still a bit new to scripting so I really appreciate the help and advice! 

    Cheers!

    • strawberry_ocean
    • 2 yrs ago
    • Reported - view

    Fred I think I figured it out! Here is my solution, I think I just had to direct the "last" function to actually look at the Item History table from the Item point of view and not the transaction which makes sense, I just didn't realize I could hop that many times back to the Item History.  

    let x := last('Item History'.Item.'Item History' order by Date);
let xTIDate := x.Date;
if Date = xTIDate then
    switch Type do
    case 1:
        'Item History'.Item.(Status := 4)
    case 2:
        'Item History'.Item.(Status := 5)
    case 3:
        'Item History'.Item.(Status := 6)
    case 4:
        'Item History'.Item.(Status := 1)
    case 5:
        'Item History'.Item.(Status := 8)
    end
else
    alert("CAUTION: This transaction is not the most recent for some or all of the items in this transaction.")
end

    Thank you so much for the help, it got me going in the right direction and with a little trial and error, I was able to get it working! 

    Let me know if you think there is a better way, but I am so happy this is working now! 

    Cheers! 

Login to reply

Content aside

  • Status Answered
  • 1 Likes
  • 2 yrs agoLast active
  • 3Replies
  • 177Views
  • 2 Following