Re: Display number of days between date and curren...

Monns · 07-07-2020 10:50 PM

Hello,

I am trying to use lookup and grab the date from table A. Then I am trying to create a formula that will count the days between that date from table A and the current date and display on table B.

Can you please show me how, been stuck for a while now.
Thanks

Suvrutt_Gurjar

You may wish to mention what column you are looking up from B table in the corresponding column in table A.

In general the LOOKUP() expression can be
LOOKUP([_THISROW].[Table B Lookup Column], “Table A”, Table A Lookup Column", “Table A Date Column Name”))

Say the above column is called [Table A Date] in TableB

Then difference in days you can find by

HOUR ([Table B Date Column] -[Table A Date])/24

Relevant articles for reference

Monns

thank you, yes I was vague.

Table A name is “Contact” and the name of the column is “Date” and then table B is “Record” and I am saving the date to the column named Days on table B. So if I got it right my formula is:

LOOKUP([_THISROW].[Record], “Contact”, Date", “Days”))

I am getting an error

Suvrutt_Gurjar

Thank you. However you may wish to search the date in Table A against some parameter such as say " Order ID" , meaning search the date of that order in Table A where the “Order ID” of this record in Table B matches.

Please go through the article on LOOKUP()

Monns

Thank you!!!
I’ve gotten somewhere and hit a road block again.

I am trying to create an order, once it is created I want to be able to see how many days it was last edited: Example: edited 3 days ago

so as I mentioned earlier my date column is on my contact table, using lookup I grab it from there to my Record table under column date_from_contact. I used this formula:

LOOKUP([Date], “Contact_txn”, “date_from_contact”, “Date”)

Then on my Record table I used this formula to get the number of days it was last edited:

HOUR(TODAY() - [date_from_contact]) / 24

Problem:
I am constantly getting 18,450 days and I am assuming it is because it started computing even before I have created the order. How can I create the order first and then get the number of days it was last edited.

Suvrutt_Gurjar

Thank you.

As mentioned in the article , the LOOKUP() works in a manner where you match one value having common pointer column that has many values for that pointer in the look up table to get a different parameter as return from the lookup table.

LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")

So in the expression above “Bob’s Burgers” and “Name” are common pointer parameters. The expression looks for restaurant name “Bob’s Burgers” in the list of restaurant names column called “Name” in the table Restaurants and returns back the “Phone” for that record from the [Phone] column in lookup table “Restaurants”.

In your expression common pointers as well as return value all seem to be dates.

Could you please mention where the highlighted two date values are in Table A or Table B

which table [Date] is there and which table "Date"is there in the below expression?

LOOKUP([Date], “Contact_txn”, “date_from_contact”, “Date”)

and what result you are getting for this expression in expression assistant?

Edit: Or you may wish to share a screenshot of some sample records with dummy data from your both the tables to get an idea of table structure.

Monns

ooohhh is the lookup formula only used once then because I have a whole lot of orders I wanted it to work on? Sorry I am getting confused.

So this is my table A where I am getting my date from, it is a ref from Table B

I did not get any errors with that lookup formula, though I get what you mean now. This is what expression assistant says:

Note, this expression could significantly impact sync time.

One randomly chosen value from this list (
…The list of values of column ‘Date’
…from rows of table ‘Contact_txn’
…where this condition is true: ((The value of column ‘date_from_A’) is equal to (The value of column ‘Date’)))

Steve

You need an expression like this:

(
  HOUR(
    TODAY()
    - LOOKUP(
      [_THISROW].[tableB-column],
      "Contact",
      "tableA-column",
      "Date"
    )
  )
  / 24
)

Replace tableB-column with the name of the column of the row in table B that contains a value that will uniquely identify the row in table A that contains the Date column value you want for that table B row.

Replace tableA-column with the name of the column of the row in table A that already contains the value you’re looking for with the value in tableB-column above.

Ideally, the tableA-column column would be the key column of table A, and the tableB-column value would be a Ref to table A. In fact, if it is a Ref, there’s an even easier way to do this!

See also:

Monns

Thank you! This code is concise and get me what I need.

Only now, the days always only display DaysLastVisited: 18,451.

I am not sure what to change at this point because I feel like it should be working?

Steve

Please post a screenshot of the expression you’re trying currently.

Monns

sure:

id my unique identifier from Table B
Contact_txn name of Table A
ContactRec my ref on Table A to Table B
Date is the column I’m copying

Also just so you know what I am trying to do, this date updates constantly .

Steve

I would suggest creating a temporary virtual column for testing. For the App formula expression of this testing virtual column, enter just the LOOKUP(...) part of the expression above. The use the Test button in Expression Assistant to test the expression to see if you get the expected results.

Monns

yeah, definitely not getting anything back.
I’m missing something but I feel like I’ve tried everything.

Steve

Does the value of the id column in table B occur in the ContactRec column of the Contact_txn table?

Monns

no, I don’t think so. So on Table B, I have this column named Related Contact_txns with this formula REF_ROWS(“Contact_txn”, “ContactRec”) so I was assuming that’s my ref.

Steve

Try this in a testing virtual column:

[Related Contact_txns][Date]

Note that the testing virtual column may be deleted immediately. You can always create a new one for more testing.

Monns

I got this list back = 44 , 9027da2e , 46b14ff6

Display number of days between date and current date from 2 tables