Amend Field Calculation

[James Patrick]

Hello,

 

I am not sure if a calculation is the best way to do this but any help would be greatly appreciated.

 

Basically I have 2 tables Enquiries and Jobs with a field on each called dept. Dept is a drop down list which on Enquiries has values QA, QE, QP  and on Jobs has values A, E, P basically without the Q.

 

I have a script that when an enquiry becomes a job copies various fields (using set variable) opens a new job record and sets the fields with the data from enquiries. What I want to do is copy the dept but drop the Q when entered on the Jobs record.

 

I have tried using a calculation on the Dept field on Jobs - if(Dept = "QA";"A") but it doesn't work.

 

Does anyone know a simple way to do this? 

[comment]

Does anyone know a simple way to do this?

 

I believe the simplest way would be to have one table for both enquiries and jobs, with a type field distinguishing between them. Alternatively, you could create a related record in Jobs when necessary, and use the original data from the related enquiry. Moving/duplicating data among tables is redundant and error-prone.

 

The next simplest way would be to use the same value list for both (whether job or enquiry, it is the same department, isn't it?).

 

Finally, you could strip all Qs from any entered value/s by =

Substitute ( Dept ; "Q" ; "" )

Edited December 5, 2013 by comment

[James Patrick]

Hi Comment, thanks for the response. I originally had them as one table but it was getting really crowded as I have a lot of filters for both enquiries and jobs and I though it would be clearer if the person could just click on the enquiry table and see purely the enquiries or go to jobs and see purely the live jobs. I know that you can do this with filters but like I say there is quite a lot going on already on the Jobs table.

 

Could I use the substitute command if I wanted to keep the tables separate?

 

Thanks

 

James

[comment]

Well yes, if you want to do it the hard way...

[David Jondreau]

Personally, I'd probably do it with two tables. It depends on what information you collect on Jobs that you don't need for an Enquiry and if an Enquiry can resul tin more than one Job. But I'd use a foreign key in the Jobs table for the related Enquiry ID. Then have auto-enter calculations fill in the Enquiry information rather than scripts. Your auto-enter calculation can contain the Substitute() function.

[comment]

But I'd use a foreign key in the Jobs table for the related Enquiry ID. Then have auto-enter calculations fill in the Enquiry information

 

That was my #1.b suggestion above - but I don't see why you would "have auto-enter calculations fill in the Enquiry information", unless it's something you want to preserve, for example:

 

Enquiry::QuotedPrice  = $1,000

Job::AgreedPrice  = $750

 

Somehow, I don't perceive the same need for:

 

Enquiry::Department  = QP

Job::Department  = P

[James Patrick]

Hello, 

 

Thanks for the replys. After Comments reply that it would be simpler to keep it in one table it really got me thinking about my layout and how people will be using the database. Please see attached work in progress which I hope explains what I am trying to do a bit better. The Jobs page as I said before was getting a bit crowded and needs the option to be able to expand it in the future so I set up another table for enquiries which gave me a simpler system, I felt, for people to use as you can see from the menu items. Previously the enquiries was part of the jobs layout and shown on another tab within the page. 

 

From Comments suggestion, that it is simpler to keep it all as one table, I realised that I could keep the layout as I have it but instead of enquiries based on the enquiries table I could base them on the jobs table. We do get lots of enquiries that don't turn into jobs and I wanted a list layout that at a glance you could see live jobs and live enquiries. When an enquiry turns into a job you only need to copy a few fields over to Jobs and then fill in extra information. Having the QA, QE etc helps to filter the enquiries from the Jobs.

 

Any feedback on the approach I am taking would be great as it is getting quite complicated. I would be interested in knowing whether people felt that the way I have separated the enquiries from a user point of view is a good idea.

 

Thanks

 

James

 

 

In Prog.zip