Conditional expression / Xbasic / Syntax help please

[bmerrison]

Hi All;
Thanks in advance for any contributions on my issue and apologies for my poor explanation of the problem

I have a vehicle parts application table with a series of related fields that refer to [grouped] variations of a particular aspect, for example;

Fh12_from [model year] > Fh12_To[model year], then Fh13_From [model year] > Fh13_To[model year] and so on

My issue is how to correctly format an expression to check if a field has an entry or is blank [Null], then only if there is some value in the field to step through the following processes.

I want to create a single additional calculated field for a record that only processes fields that have an actual entry value, with each sub-group separated by "comma-space"
[FH12 (from) > (To), FH13 (From) > (To), FH16 (From) > (To), etc]
so if a value exists then add it together with the correct text prefix, if no value, then ignore the field.
I can just concatenate all the actual fields together using;

alltrim("FH12 "+ Fh12_From)+", "+alltrim("> "+Fh12_To)+", "+alltrim("FH13 "+ Fh13_From)+", "+alltrim("> "+Fh13_To) [and so on]

This initial code seems ok for a small part of what I need but after many failed attempts and much research of alpha documentation / videos , message board etc, I cannot work out the way to add the conditional aspect and avoid all the unwanted / incorrect text and comma separators this produces

All fields are specified as 'char' type and I need the text prefixes to be only used within the calculated field[s] and of course with the correct corresponding data field entry

Hope this makes some sort of sense, of course I may be trying to go about this altogether wrongly in which case any thoughts / guidance would be appreciated

The resulting calculated field[s] are for display purposes only on a desktop form, as part of a report and within a web application if that helps with context

Thanks again

Bm

[Stan Mathews]

Re: Conditional expression / Xbasic / Syntax help please

Using variables rather than field values to illustrate.

Fh12_From = "ABC"
Fh13_From = ""

Fh12_To = "DEF"
Fh13_To = ""


? left(if(alltrim(Fh12_From)> "",alltrim("FH12 "+ Fh12_From)+", ","")+if(alltrim(Fh12_To)> "",alltrim("FH12 "+ Fh12_To)+", ","")+if(alltrim(Fh13_From)> "",alltrim("FH12 "+ Fh13_From)+", ","")+if(alltrim(Fh13_To)> "",alltrim("FH13 "+ Fh12_To)+", ",""),-2)
= "FH12 ABC, FH12 DEF"

You could also do a series of calculated fields for the report and form.

pt1 = if(alltrim(Fh12_From)> "",alltrim("FH12 "+ Fh12_From)+", ","")
pt2 = if(alltrim(Fh13_From)> "",alltrim("FH13 "+ Fh13_From)+", ","")
pt3 = if(alltrim(Fh14_From)> "",alltrim("FH14 "+ Fh14_From)+", ","")

and then

calc_final = left(calc->pt1+calc->pt2+calc->pt3,-2)

[Ted Giles]

Re: Conditional expression / Xbasic / Syntax help please

This looks like the old Effectivity Date for components.
It can ge difficult, but if you have the start and end date for an item, possibly linked to the parent item, it can work.

More examples of data?

[bmerrison]

Re: Conditional expression / Xbasic / Syntax help please

Firstly, thank you for your swift replies and interest in my current issue, many thanks to Stan for the code which I admit that at the moment I do not fully understand, but I will study it further and try it out when I am back in the office [I get the general concept and I am confident it will do the job for me but the finer details elude me at the moment such as where the "-2" at the end fits in] I stress this is totally my failing in not being far enough along the xbasic learning curve!

Regarding the start and end dates, each part has a series of vehicles and model years that it fits, so if you take a model of vehicle such as an "FH" [Volvo Truck] then within that range are a varying degree of different models such as "FH12" "FH13" "FH16" which further split down on years to "FH12 1994>1999" then "FH12 1999> 2001" "FH12 2001>2005" FH12 2005>2008" "FH12 2008>2012" with a similar range of [differing years] for FH13 and FH16 and again throughout the manufacturer model range, so any one part number may fit any intricate combination of years within a single model [FH12, say] and also a different combination within other models and often fits other manufacturers / models each with their own range of years just to complicate things!

There is a requirement to display a 'summary view' on the desktop / report and within a webapp of what any one part is listed as fitting. To do this I need to add the relevant model plus its 'Year_From' & 'Year_To' [but only if the part fits that particular combination] as text by way of an expression to construct the summary. There are other similar part fitment groups requiring the same summary which work on the same principle, so if I can crack this one with the help of the community it will help me a huge amount elsewhere within the project

Apologies once again for my lack of explanatory skills, all assistance is very much appreciated

Kr

Bm

[Stan Mathews]

Re: Conditional expression / Xbasic / Syntax help please

where the "-2" at the end fits in]

Left("bryan merrison~~",-2)
removes the final two characters yielding

bryan merrison

Since we don't know how many terms will be used we always put comma-space at the end of each and trim off the final two characters.

I think my expressions used space-comma instead of comma-space but you can easily adjust those.

[Tom Cone Jr]

Re: Conditional expression / Xbasic / Syntax help please

It will help us help you if you supply the table structure(s), and some sample data, along with an example of the result you'd like to obtain from the sample.

-- tom

[MoGrace]

Re: Conditional expression / Xbasic / Syntax help please

Nt

[Ted Giles]

Re: Conditional expression / Xbasic / Syntax help please

Bryan, have a look at the attached. Might give you some ideas.

[MoGrace]

Re: Conditional expression / Xbasic / Syntax help please

Hi Ted,
That was my first thought too on how to show a many to many relationship with an intermediary table. But with parts, wouldn't there be potential overlaps where the date range applies to 2 years for one model but 4 years for another? So now a date range must be included for a particular part and not just for a model. It could get confusing with multiple parts...

[bmerrison]

Re: Conditional expression / Xbasic / Syntax help please

Hi everybody;

Firstly to pick up on the last comment, yes this is exactly the case with our inventory and there are overlaps and complex fitment details.

I can see the logic and virtue in Ted's example and this is something I will definitely be looking to take forward for development, but time is a real issue and with this in mind I have successfully used the code from Stan to implement the first range of required parts.

I have a number of similar ranges to deal with, but I am now reasonably confident I can understand and edit the code to suit [famous last words spring to mind!]

So a huge thank you to Stan for the code and also the assistance and example from Ted and of course the contributions from all of you who take the time to help people like me with our issues, it genuinely is very much appreciated

Kr

Bryan

[Tom Cone Jr]

Re: Conditional expression / Xbasic / Syntax help please

I'm wondering if each part should appear in multiple records. One for each combination of model and year.

[MoGrace]

Re: Conditional expression / Xbasic / Syntax help please

I'm wondering what the application is about. If only to create a list to view, you still have to get the data into the system; so how to present the data to the user who does this input is what puzzles me.

In my application I must match Machines to the Needles they can use based on multiple factors that have to be matched in the current machine setup to the needle. The mechanic needs to see this list of needles to make his selection.

I don't know if a car might use more than one particular timing belt, eg; but I can understand how a particular timing belt might fit more than one model of a car.

[Ted Giles]

Re: Conditional expression / Xbasic / Syntax help please

I'm with you you Robin. Parts are based on a vehicle component list which is linked to the chassis and/or engine number. Date ranges are not always relevant as changes take place mid year.

[bmerrison]

Re: Conditional expression / Xbasic / Syntax help please

Hi Guys;

To answer the comments;
It is quite right that in the car industry you can in general link a part to a chassis / engine number relatively easily, but in the Truck industry there are a number of extra complexities.

Taking the timing belt analogy, then yes one belt can fit many models, but also the same engine may be found in other brands and the belt may fit another brand that is not common knowledge, for example some Volvo's have ford diesel engines fitted and certain parts are identical but a lot cheaper if you buy them for a Ford rather than a Volvo, this was the case many years ago when Volvo used Renault petrol engines in some of their models. The chassis / engine numbers alone are of no use in this situation. The other relevant fact is there are many applications in the car marketplace which can match a reg number to relevant parts, but there is little or nothing suitable for our needs from the Truck manufacturer's or the part manufacturer's themselves, the data is all over the place, so we have developed an Alpha 5 application to deal with this data from wherever and in whatever type it comes, we can access it and process it in a number of different ways to suit our various needs, the display list that I was struggling with coding for is a small part of a couple of the application outputs and not directly relevant to the user data entries

In broader terms of what the application is for, I have only touched on a small part of a number of things it needs to do. The different uses are closely linked and would take a lot of explaining for no real gain as it is a bespoke application quite literally to suit just our company and the way we operate in the market

Thanks again for all the interest and contributions

Bryan

[MoGrace]

Re: Conditional expression / Xbasic / Syntax help please

That is quite a task Bryan! I hope you will let us know how it worked out.