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

Monns
Participant I

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

0 16 772
16 REPLIES 16

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


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

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()

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.

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.

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โ€™)))

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:


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?

Please post a screenshot of the expression youโ€™re trying currently.

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 .

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
Participant I

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

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

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.

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.

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

Top Labels in this Space