Lookupd()

[John Burke]

Hi, have looked at threads regarding lookups, didn't find what I needed. Have a talbe "receivble.dbf", two of the fields are, companyid, due_date. I am tryint to put a simple exp in data entry, on the due_date field. Exp is "date+LOOKUPD("f",companyid,"terms","company.dbf","company1") result is "does not evual to date value", going wrong here somewhere. Basically I need the due_date to be the entry date plus whatever the terms are for the cust. Ant suggs. tks jb

[Stan Mathews]

Re: Lookupd()

Did you mean to use

"date()+LOOKUPN("f",companyid,"terms","company.dbf","company1")

You match the lookup type to the value you are returning.

[Tom Cone Jr]

Re: Lookupd()

John, leaving aside the typos in your post, are you trying to add a number to the present system date to determine when something (a payment, perhaps) should be due?
If so, shouldn't you be looking up a "number", and not a "date". Lookupd() will return a date, not a number. Don't you need a number? i.e. shouldn't you be using LOOKUPN() ?

[John Burke]

Re: Lookupd()

sory forgot the zip

[Stan Mathews]

Re: Lookupd()

Rather hard to see what you are trying to do without any data. Your terms field is character type.

The lookupn() failed because we assumed your terms field was numeric.

date() + 30 yields {06/05/2009}

date()+"1% Net 30" yields "05/06/20091% Net 30"

I think you need to reexamine what you exprect to happen.

addendum: If you want to use the system date variable you must refer to it as system->date, not plain date.

[John Burke]

Re: Lookupd()

Thank you Stan, changing the lookupN to lookupC, gave me the expression result of / /. However, when I go to save it, still says it doesn't evual to a date value. If I put system->date, in front of date, it gives me 06/05/2009, but still says is not a date value, Puzzled, thans for the patience. jb

[Tom Cone Jr]

Re: Lookupd()

John, I think it would help if you wrote down the small steps you want Alpha Five to take so we can understand what it is you're trying to accomplish. How about explaining it in words, walking through the steps you want Alpha Five to take... like this:

a) retrieve _______________ from the __________ table, using index ___________ and
a search key of ______________

b) "add" ____________ to the current system date

c) store the resulting "date" in field ____________

[John Burke]

Re: Lookupd()

Hi Tom, tks for trying here. Here goes, 1.. I need to look at the date field in the receiv table. 2.. then I need to lookup the companyid from the same table "receiv". The companyid looks up the cust.dbf and retreives the Terms for that customer. the index is company1. 3.. The result is stored in the due_date field. Right now I'm trying to use this expression.

date+LOOKUPC("F",CompanyID,"TERMS","Cust.dbf","Company1", this gives me the result of / /, but doesn't save as a date value. tks Tom, jb . attached are the 2 tables involved.

[Doug Page]

Re: Lookupd()

John, I think what Stan and Tom are trying to point out here is for you to look at the data in each of the fields that you are using and try your expressions manually. For instance, are you trying to do your date calculations from todays date? If so, why are you looking up a date in a table. If your terms field has something in it like "1% Net 30" then what are you expecting if you add that character value to a date. When getting an error like you did you need to break things down into parts. The expression that you are trying to complete is to add a date to ?????? If you look at the docs, you add a numeric value to a date to get a resulting date. Your terms field is character. So that won't work. Now when you look at the values that you are going to have in your terms field, can they be evaluated as numerics. If not, then you need to rethink what you are doing and how to calculate it.

[Keith Hubert]

Re: Lookupd()

Hi Doug,

What I think John is trying to do is calculate the date an invoice is due from todays date plus the number of days in the terms of the selected customer.

You are right that John has the terms field as character, so if you change it to numeric and enter 28, can you get the lookup to work?

This works for me, DATE()+LOOKUPN("F",Companyid,"Terms","cust","Cust_Id") result = 04/06/2009 where terms are 28.

[Tom Cone Jr]

Re: Lookupd()

John, the step by step explanation is a good start. I've taken a look at the tables you supplied. In the Cust table you gave us the "terms" field is blank. So we don't know what kind of data you would normally store there.

In the Receiv table the "date" field is blank in many of your sample records and is the same the remaining records.

Please re-write your step by step sequence but include actual sample field values so we can see how you finally get to the result you are seeking.

It seems to me your narrative description skips a step or two. Retrieving a text string from the "terms" field will give you a text (character) result. You then say you want to put that result in the "due_date" field, but "due_date" cannot hold character data. Somewhere a step got skipped.

Please break this down into smaller, more numerous steps, using actual field names and sample field values, so we can get a grasp on what it is you're trying to do.

I know this seems tedious. But understanding the question is the key to finding the answer.

-- tom

[Stan Mathews]

Re: Lookupd()

John,

I'm going to try to work back at this from your perspective/data.

1.. I need to look at the date field in the receiv table.

The second record is for companyid "00001", the date field holds 05/05/2009.

2.. then I need to lookup the companyid from the same table "receiv". The companyid looks up the company.dbf and retreives the Terms for that customer. the index is company1.

Currently there are no terms set in the company.dbf for companyid 00001. One of the items in the simple list lookup you have defined for the terms field is "Paid" and another is "1% Net 30".

Let's examine those two cases.

3.. The result is stored in the due_date field.

What should be stored in the due_date field for a transaction dated 05/05/2009 for companyid 00001 when the terms are "Paid". What should be the result of 05/05/2009 + "Paid" ?

What should be stored in the due_date field for a transaction dated 05/05/2009 for companyid 00001 when the terms are "1% Net 30". What should be the result of 05/05/2009 + "1% Net 30".

If you add a "days" field to the company.dbf, make it numeric, and define some "days", this works. (Make sure your company1 index has the expression companyid.)

DATE+LOOKUPN( "F", COMPANYID, "days", "COMPANY.DBF", "COMPANY1")

The reason your date field was confusing is that most here have become accustomed to not using Alpha function names as field names. Instead of "date" we might use "tdate", "transdate", "t_date", "trans_date", or something similar.

[John Burke]

Re: Lookupd()

Thank you all for your input. I believe I tried to make a simple result into a complicated process. I changed the "terms" to a N value, thank you Keith, and the results are as required. Now I just have to add a discount field to handle the % discount based on how may days it takes to pay the Inv. Again thank you all. jb