Phone Log, pull date if field meets criteria

[Matthew R White]

I have a large database, with a separate relationship/table called phone log, we store all phone logs in this table and it is linked to the main database via the Customer Number Field.

This phone log stores auto-enter dates, typed phone log messages, has a container field for documents, and a Document title field. etc.

 

within my main database, I'm trying to create a field that says (if the document title contains the word "application" then display the auto enter date that corresponds to this record.

 

Any thoughts?

[Ocean West]

Hi Matt,

 

In the log table create a field  isApp  log::document_title = "application" 

 

Only the records in the log table that are "apps" would have a boolean flag of 1 if it is true

 

In your main table you could have a field "constant" that = 1 then create a multi predicate key relationship

 

customerNum = customerNum AND contant = isApp 

 

you could then create a calculation in the main table Max ( DATE_AUTO_ENTER) this would give you the Maximum date of the applications should you have multiple in the log table.

 

There are possible other ways to tackle this but need more caffeine  first. 

[Matthew R White]

Thanks Ocean West! 

So, if I'm understanding correctly... create a field in the Phone Log Data Table, as a calculation field with the calculation isApp  phonelogdata::document_title = "application" 

 

Then create another field in the main table that is a calculation field also with the calculation: customerNum = customerNum AND contant = isApp 

 

Then create the auto enter date calculation?

 

I'm a bit of a novice so some things are hard to follow.

 

My original thought on this, was to create a duplicate phone log data table, and create a relationship between the document field, and create a new document field in my original database. then within the original database pull the date field from the phone log data table that matches up to application... but it hasn't worked so far

[Ocean West]

Hey Matt, 

 

Here is a quick sample file that may better show the structure for what i think you are trying to achieve.

 

phoneLog.fp7.zip