Date Calculation

Help Center Get Answers
1

Hello (simpleton here, please be gentle!), 

I have the need to track an asset. 

I have two objects in my database, 1) The Asset and 2) The Activity. 

The Asset is a fixed purchase with known details such as size and date of manufacture. 

The activity is "connected" to the asset to allow the listing of location (it changes regularly), any repairs to a particular asset etc. IT all works well except for one critical part:

I need to record 12 monthly inpsections and be able to search / flag up any asset that is about to expire its 12 months. In the first instance I know the date of manufacture so I can easily know the next inspection due date by using the equation of "date of manufacture" + 365 days. Where I am struggling is for assets that are a few years old - they will have more than one 12 month inspection added in the (2) activity object. How can I make it so that the system only searches for the latest date which could be either the date of manufacture in (1) or the 12 month inspection in (2). 

Desperate for help! 

thanks

2
Hi Bobby,

Thanks for reply.

I will check into Knack-implementation and provide feedback.

Hopefully other community members will have idea(s) with the above pictures to contribute in this discussion.

Thanks,
Neal
NealPatil @ gmx.com

 

3

You are correct, thanks for the help! This is very feasible in a spreadsheet as you can see yourself I was just trying to do it in Knack.

Any ideas?

Bobby

4

 Hi Bobby,

Thanks for reply.

You would like to create formula for Promise Date for SalesOrder and it will be determined by the latest shipping date of SalesOrder-Product.

In case shipping date of product of sales order is not available then promise-date can not be calculated(This is my assumption).

So in the following spreadsheet screenshot prototype

  • Order1 can not have promise date because two products do not have shipping date
  • order2 has promise date = latest shipping date for products of order2 = 09/25/2016

Thanks,

Neal

NealPatil  @ gmx.com


5

Hi Neal, basically what I am attempting is:

Assume each product has a different Shipping Date. Order1 actually needs a "promise date" that is equal to the latest date of all the scheduled ship dates for line items in that order.

If Product1 ships 09/01, Product2 ships 09/25, and Product3 ships 09/15, the order is not fulfilled until the 25th when product 2 ships, and therefore I want my promise date for Order1 to be 09/25. That way the salesmen can tell their customer when the order will be 100% fulfilled. 

Does that make any sense?

6

 Hi Bobby,

I have created spreadsheet screenshot prototype to understand your needs. What would you expect for shipping-date-target for Product4 and Product5?

Thanks,

Neal

NealPatil @ gmx.com


7

Hi Bobby, 

Knacks help desk helped me out with this, it was done by having a second database called activity where it called upon the asset then it used a simple formula.

Count the number of activities (inspections)

Then an equation of

({Number of Inspections}*365)+365 

Then an equation of

{Date of Manufacture}+{Inspection Needed Calc}

8

Hi, I am also having a similar issue. I have sales orders with multiple line items but not all items ship at the same time. I want the sales order to pull the ship date from the latest item so that we don't over promise a date in sales.

I tried to start doing this by converting the dates to numbers, using a max function and then converting back but I'm having trouble converting back.

Did you ever figure this out?