possible to query another table's field and return boolean?

skycatcher · 2013-07-18

Hi everyone,

I'm working with NAV 5 right now and I seem to be stuck on something simple.

I have added a new field to the Vehicle table called RDKS. (Boolean/Normal/Checkbox)
This field is also added to the Vehicle List form.

On the Vehicle Card I have a Checkbox that shows Vehicle.RDKS and returns True or False so users can see if the field in the table is checked or not.

I also need this checkbox on the reports, such as Sales Quote, Order, etc...

Right now I'm working with Sales Quote and I cannot edit the Sales Header table due to licensing restrictions.

I've added a global var in Sales Quote:

RDKSvorhanden : Record : Vehicle

Since I can't add the field RDKS to the sales header table, I thought there may be a way to just add it to the source exp of the checkbox since the global var should allow me to use RDKSvorhanden.RDKS to access Vehicle.RDKS but that doesn't seem to work correctly.

I've tried a couple things in the OnAfterGetRecord but nothing seems to work.

All I need is for the checkbox to show true or false on the Sales Quote form, depending on what's in the Vehicle.RDKS field.

Any help would be much appreciated, thanks.

mdPartnerNL · 2013-07-19

Can't you do it first with the cronus license in a demo db. Then export and import in your test db.

skycatcher · 2013-07-19

I only have a dev and live database to work with. They have a developer license but the sales header table can't be modified :-/

jspopp · 2013-07-19

Hi, what code have you written (or tried out) on the Sales Quote form? Also, what is the link between the Sales Quote and the record on the Vehicle table? Please let us know, Thanks

Savatage · 2013-07-19

Are you confusing forms & tables?

How can you not modify the sales header but have access to OnAfterGetRecordTrigger?

The sales header table is the basis of Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order forms.

Have you tried using a Flowfield using Method:Lookup to show your boolean value from another table?

skycatcher · 2013-07-20

Sorry, I will try to be more descriptive. I've only been working with Nav for a few months and I haven't had any training, it's just been learning by doing and I still don't quite understand how everything links together. Normally, I would edit the Sales Header (Table 36) and insert a boolean field, link that to the Vehicle table's boolean field and be set (at least I believe so).

Since I cannot modify table 36, I'm not sure how to get Form 41 (Sales Quote) to show a checkbox that is either true or false, depending on what's in the Vehicle Table . booleanfield(RDKS)

In the vehicle table:

Enabled	Field No.	Field Name	Data Type	Length	Description
Yes	1028276	RDKS	Boolean

Form 41

Checkbox name
RDKSverify

Checkbox SrcExp

RDKSvorhanden.RDKS




Globals

Name	DataType	Subtype	Length
RDKSvorhanden	Record	Vehicle	
RDKSverify	Boolean	



OnAfterGetRecord


//RDKSvorhanden.TESTFIELD(RDKS, TRUE)
    //MESSAGE('%1',RDKSvorhanden."Search Name Vehicle");
//IF RDKS = TRUE THEN BEGIN
//MESSAGE('%1',RDKSvorhanden.RDKS);
 // RDKSverify := TRUE;
 //END
//ELSE BEGIN
//MESSAGE('%1',RDKSvorhanden.RDKS);
// RDKSverify := FALSE;
//END;

I've tried a bunch of variations but it seems that RDKSvorhanden.RDKS doesn't have a value. I would have thought that since the defined global RDKSvorhanden = Vehicle, that it should return Vehicle.RDKS...but it doesn't.

I haven't tried the flowfield method just yet as mentioned although I did find a post about it on the forums because the example confused me a little.

The checkbox in the vehicle table and on the vehicle card works fine, I just want another checkbox on the Sales Quote, Order, etc to reflect the value from the vehicle table but I'm not sure how to get the two tables to pass values back and forth. It seems I can't access any values from the Vehicle table through the Sales Header without defining the fields there

Thanks for the replies and appologies for the lack of knowledge so far.

vaprog · 2013-07-20

skycatcher wrote:

seems that RDKSvorhanden.RDKS doesn't have a value. I would have thought that since the defined global RDKSvorhanden = Vehicle, that it should return Vehicle.RDKS...but it doesn't.

Yes, you're right, the RDKSvorhanden record variable does not have any values (other than system default values) until after you fetched them from the database. How should the system know, which vehicle you want to examine? You do need to write code to do this. To give you any more detailed information on how to do this, we need your answer to the second half of jspopp's request: How is a vehicle record identified? What data do you enter into your quote/order/... to link it to a vehicle record? Is that link mandatory or optional? Is the link one to one or may it be one to many (one document may refer to more than one vehicle)? Is the data, that links the two record types on the sales header or the sales line?
Once you answered those questions you need to use the GET or FIND/FINDFIRST/FINDLAST/FINDSET functions on the RDKSvorhanden record to get the data from the database.

If the link is one to one, from Sales Header to Vehicle, (as I assume from what you did so far), then you would add the code to fetch the record in the OnAfterGetCurrentRecord trigger of the Sales Header Form (ID 41 for Sales Quote, ...) and then use the value in the control just as you did.

skycatcher · 2013-07-20

Thanks for explaining it to me in such a detailed manner, it helped a lot.

It turns out the two tables were linked by Vehicle No. and all I needed to add to the Form 41 OnAfterGetRecord trigger was

IF RDKSvorhanden.GET("Vehicle No.") THEN BEGIN
    RDKSverify := RDKSvorhanden.RDKS;
END ELSE BEGIN
 ERROR('');
END;

This seems to work as intended, returning true and false for my checkbox

Thanks so much for your help, now I can finally get some sleep!

possible to query another table's field and return boolean?

Answers

Categories