Comment Group, VLookup assistance
Hello,
I have an invoices database, with a table named Invocies, within invoices, I have a text box called "Comment"
I use the comment field to sort-of organize my invoices the way I prefer.
Followed by Comment, I also have a formula called "Comment Group"
What 'Comment Group' will do is look at Comment, and based off the result will display a text. Here is the formula I have set in 'Comment Group' currently:
if 'Invoice Status' = 1 then
if Comment like "NTC" then
styled(text("Need to Contact (NTC)"), color(242, 229, 0), "calendar2")
else
if Comment like "NEEDS ATTN" then
styled(text("*NEEDS ATTENTION*"), color(255, 0, 0), "warn2")
else
if Comment like "AWAITING SHP" then
styled(text("Awaiting Shipment"), color(66, 160, 251), "reload")
else
if Comment like "CLS" or like "CANCEL" then
styled(text("Close/Cancel "), color(127, 255, 255), "stop")
else
if Comment like "EMAILED" or Comment like "CALLED" or Comment like "NOTIFIED" then
styled(text("Emailed / Called / Notified"), color(0, 0, 0), "calendar")
else
if Comment like "P/LTR MAILED" then
styled(text("Mailed Payment Letter"), color(255, 0, 0), "mailbox")
else
if Comment like "EXTENDED" or Comment like "CNC EXTEND" then
styled(text("Extended Cancelation Date"), color(255, 63, 207))
else
if Comment like "RFP" then
styled(text("Ready for Pickup"), color(127, 255, 0), "X")
else
if Comment like "SHIPPED" then
styled(text("Shipped to Depot"), color(223, 223, 223), "dropbox")
else
if Comment like "PEND" then
styled(text("PENDING"), color(255, 191, 0), "hammer")
else
if Comment like "ORDERED" then
styled(text("Ordered"), color(223, 223, 223), "delivery")
else
if Comment like "BKO" then
styled(text("Backordered"), color(251, 177, 65), "clock")
else
if Comment = void then
styled(text(""), color(255, 255, 255))
else
styled(text(Comment), color(170, 170, 170))
end
end
end
end
end
end
end
end
end
end
end
end
end
end
Quite long, yes I know. 'Comment Group' is so important for me, becuase I have a kanban view which organizes each 'Comment' or 'Comment Group' into a special place, based on what the formula displays.
Here is my goal:
I would like to create an additional table where I can create my own 'Comment Groups' without having to modify the data. I'm looking for something like Excel's VLOOKUP, for example I have in my new Comment table 'Search For' where Invoices.Comment 's result will search Comment.'Search For' then once it finds what matches, it will display my second field which is 'Show As' (which the result of 'Show As' will replace my existing Invoice.'Comment Group'
I just do not know how to write up this formula that will take my result, and search an additioanl table, and based off of the results display something in my formula.
I hope someone understands what I am trying to do, and is able to assist me with a solution, I know this sounds complicated.
I also attached the new Comment table I made that I would like to use.
'Search Order' is the order I would prefer the VLOOKUP to search for. As my Invocie.'Comments' may have different wording, like "NTC by TMR / CANCEL 12/01" which currently would just group as "Need to Contact"
16 replies
-
My goal for this is to reference a table, instead of use a formula for my 'Comment Groups'
-
Well, it's not exactly in line of your case but what i do in this example is getting the name of the person with the corresponding logincode from another table. Have a look if you can use this idea. (Link below) So what i do is first make an array of the existing login codes and then get the corresponding name belonging to that code. NXCode is in the Enter button. There are some hidden fields in the startscreen also and in the top formula field also. In the database trigger after open option is also code to open the startscreen on startup.
https://www.dropbox.com/s/akvy47ls9jgjn59/LogInSystem.ninox?dl=0Steven.
-
That actually helped quite a bit! So far I have:
let o := concat((select Comment).'Search For');
let c := Comment;
concat(select Comment where 'Search For' like c)It is pulling the id to Comment! I am trying to get it to display the 'Show As' but it is not working, so far i've tried:
let o := concat((select Comment).'Search For');
let c := Comment;
concat(select Comment.'Show As' where 'Search For' like c) -
So, I also figured out that
let o := concat((select Comment).'Search For');
let c := Comment;
first((select Comment where 'Search For' like c) order by 'Search Order')with call the correct 'Comment Group' based on the order I specify by using "order by 'Search Order'.
I also noticed an issue with this, even though I have "like" instead of "=", it has to be an exact match to populate the result. How can I fix this?
-
Made this. Formula is in the trigger after update of the 'Search For'-field in the Comments table. Also in the Source table the field short contains the upper formula to make input easier.
https://www.dropbox.com/s/b6okz9qtey0nb14/Kmoore.ninox?dl=0
Steven.
-
Thank you, that did help!
My next issue is in my Comment text box, it is not always identical to call the group.
With the formula you setup, in order for it to call the exact formula, comment has to be (for ex.) "NTC" it cannot be "NTC by tomorrow" otherwise it will not populate a result.
-
I see,
is the search word always on the left? Then you could use this:
let s := upper(substr('Search For',0,3));
let n := first(select Source where Short like s);
'Group Name' := n.GroupNameSteven
-
Not necessarly the first characters. On the original formula I use above, it goes in order on what to select as the Comment Group. Which is why I incorportated in my new Comment table 'Search Order', to sort of specify the order it searches the calls/conditions.
I would like it to check my Invoices.Comment, and search for the calls in that order ('Search Order').
With my original formula, it goes in an order if NTC then _ else if NEEDS ATTN then _
Like if I have a comment: "CANCEL 01.10 / NTC by TMR" my original referenced formula above will group that comment as "Need to Contact" because that is the order it searches, NTC isnt necessartly the first in the Invoices.Comment, although it takes priority over the other groups because it is the first condition.
-
just trying ...
let s := upper('Search For');
let n := first(select Source where contains(Short,s));
'Group Name' := n.GroupName -
Unfortunately no luck.
-
I THINK I FIGURED IT OUT! let o := concat((select Comment).'Search For'); let c := Comment; let i := first((select Comment where contains(c, 'Search For')) order by Order); i.'Show As' I switched places for 'Search For' and c
-
Okay, here is the final result:
let o := concat((select Comment).'Search For');
let c := Comment;
let i := first((select Comment where contains(c, 'Search For')) order by Order);
if Comment then
if i then
i.'Show As'
else
styled(text(Comment), color(170, 170, 170))
end
else
styled(text(""), color(255, 255, 255))
endThis will search for any matches in my 'Comment' table, if there is a result, it will display the FIRST result in the order listed in 'Comment' by examining the field 'Order'. If there is not any results, but Comment field is filled, it will display the exact result in the Comment field. Finally, if Comment=void, it will display as blank.
Thank you Steven for your help!!!
S. Moore
-
You're welcome, glad you found it.
-
@Steven That is SO Cool!!!! I really appreciate how everybody shares these demos (and knowledge).
... the demos and screenshots have been helpful beyond belief and I've learned so much from the people here who continually amaze me with their knowledge and willingness to share/help!
I do believe in 'paying it forward' and look forward to the day I can "coherently" help others here as well. :-) ... Meanwhile, back to @Steven 's awesome demo! Karen
-
Thanks Karen for the compliments, it feels good and that's why we keep going. Don't miss this one:
https://ninoxdb.de/en/forum/use-cases-5abd0b9c4da2d77b6ebfa395/simple-log-in-system-with-datetime-registration.-5e11d00509d132371a594f5c
Steven. -
@Steve Thanks! :-)
Content aside
- 4 yrs agoLast active
- 16Replies
- 2790Views