Query Help

bucobill

I don't know if anyone may know but if you could help it would be appreciated.

First: I have a scenario where you have 2 prices for an item in a query.

One item may be temporarily on sale but we don't want to show both items in the query. We would like to only show the lower priced item in the query.

We have tried the following in the query but it doesn't seem to work.

ThisItem.Price<OtherItem.Price

We thought this would show only the lower of the two priced items but it doesn't. Does anyone have an idea how to do this?

Second: Has anyone every run a query that shows a log of all Instant Messages in one screen like a running conversation where each time the IM comes in the previous messages are displayed above?

Thank you guys as always.

pureist

are your two item prices held in 2 attributes of the item object, and thus a query showing all items (all attributes thereof) would display 2 columns for the two prices?
or, are the 2 item prices held in a reference attribute owned by the item object (thus allowing you to add any number of prices, their validity dates, etc.)?
ie. is it a master only type structure, or master-detail type structure?

BenHayat

pureist wrote
are your two item prices held in 2 attributes of the item object, and thus a query showing all items (all attributes thereof) would display 2 columns for the two prices?
or, are the 2 item prices held in a reference attribute owned by the item object (thus allowing you to add any number of prices, their validity dates, etc.)?
ie. is it a master only type structure, or master-detail type structure?

His post is a bit unclear on the price and item. But I gather there is one attribute for price, but there might be one or two or more copies of the same item with different prices.
Item 1 - Price: $20
Item 1 - Price: $30
Item 1 - Price: $40

If that's the case, it's best to group the items by Item Name and then sort by Item price and pick the first from group. This will give you the lowest price.

bucobill

Yes it is one attribute. If we do the group would we still show other items? Since we have multiple types of items but only want to display the lowest rate available for each. We use the query to select the items and then add them to the order. I also like the idea of the second attribute which may fix another issue where we allow sellers to enter their own rates even if the item already exists in someone else's store. But once again we would only want to show the best priced one.

customaware

Hi Bill,

This how I would do it.....

I have 3 Attributes in the BO

Price1
Price2
DisplayPrice

And a Rule

IF Price1<Price2
THEN DisplayPrice=Price1
ELSE
DisplayPrice=Price2

And then..... Only display the DisplayPrice in the Query

Alternatively, you could use a Custom Query which the HTML can have something like and of course styled with css any way you want.

<<IF (Item.Price1<Item.Price2 THEN SHOW SECTION_START>>
<div>Price:   <<Item.Price1>></div>
<<SECTION_END>>
<<IF (Item.Price2<Item.Price1 THEN SHOW SECTION_START>>
<div>Price:   <<Item.Price2>></div>
<<SECTION_END>>

tford

Assuming you have 1 record per item / price and multiple records per item if you have more than one price:

add an attribute to each instance of the Item BO called LowestPrice_YN
add a rule that would set Item.LowestPrice_YN to Y is the instance is the lowest price for the item & N if the instance is NOT the lowest price for the item
Query can then show lowest prices for each item using FIND Item WHERE Item.LowestPrice_YN='Yes'

BenHayat

bucobill wrote
Yes it is one attribute. If we do the group would we still show other items? Since we have multiple types of items but only want to display the lowest rate available for each. We use the query to select the items and then add them to the order. I also like the idea of the second attribute which may fix another issue where we allow sellers to enter their own rates even if the item already exists in someone else's store. But once again we would only want to show the best priced one.

As Mark said, it's best to have one object (Item) with multiple prices (Retail proce, discount price, dealer price, promotion price and etc) and deal with one object at runtime.
But if you have to have multiple items, then you can group them by category and then by name to narrow it down to the item(s) you want, sort it by price and pick the first as your lowest price.

bucobill

Thank you guys greatly. Not to press the issue on the second part do we know any way to show an IM log within the conversation. I have a query that shows all IM's where you have the same response inside of a query but it doesn't carry the conversation and show everything said as a continuous log.

Although I have been working daily on this program for four years I still have instances where I cannot get it to do what I want. I know the program should it is just lack of knowledge on my part.

customaware

Bill,

Can you please provide more explanation for the IM issue and maybe a screenshot.

I am a bit thick but I dont get it. 😉

bucobill

Just an Instant message log in a similar way that your I phone works with text messages. Something where you have 2 people conversing and if it is today or a conversation between the two from two years ago every IM could be scrolled through in the IM window.

So you have me and you speaking today and I say " I need help" you reply "with what" I say "doing a query"

In the next message you go to type you could scroll up and see all three of our previous Instant messages. If a week or 2 passes before we IM again then when you enter my name the log would appear with our previous conversation. I hope this better explains what I am trying to accomplish. I do not have a screenshot to display since all we can do is the Instant Message one reply at a time currently. Also once again thank you all greatly for your help tonight.

customaware

Old way just like on a Phone...

Query on is

FIND All People

Click On the Person you and then you see a a sub query of all the past messages with the person either ASC or DESC order

New Version 7 way...

Start with same Query as above but Exandable. Click on Expand and then see subQuery of all the messages open up in between the top Query records...

Is that what you mean?