Formula Help Needed

1

I have been struggling with this formula and cannot get it to work. I have two databases one has a master list of items and the other has items on hand. What I am trying to do is get the master list to indicate a YES or TRUE value if the items are in the on hand database. The formula i have tried is-

If(thisRowthisRow.[Item Name].[Item Number]Contains([On Hand Log].[Item Name][On Hand Log].[Item Number].“yes”), , )

I have tried it several different ways and always get errors. I’m sure its simple, or there may even be a better formula to use so im hoping someone will know the right way to go.

Thanks in advance for any help!

Terry

2

Hi Terry. Any chance you can share a copy of your doc, or perhaps just a quick mockup that replicates the problem you are trying to solve? Will be easier than us peppering you with questions about the context. :smile:

3

image
image1298×1208 78.7 KB

This is an example of what I am trying to do.

4

At first glance, I would recommend changing your architecture, and having a single database table with multiple views.

You would have an “On Hand” checkbox column, and you would create a view of the table called “On Hand Items” filtered to “On Hand” is checked.

Are there other aspects of your setup that would prevent doing something like that?

5

The way they are wanting me to do it is to have all items shown and not filter out the ones that are not checked. The actual doc is much more complex than what I have shown but its specific to my companies operation and so I cant show the actual doc for an example. This is just the easiest way I could explain what I am trying to accomplish. :slightly_smiling_face:

6

Gotcha. I would still recommend thinking hard about whether Views is the better solution here (knowing you can filter, and also show/hide columns that are relevant in different circumstances). Speaking from experience, the kind of data duplication you’re describing is a recipe for future sadness. Happy to advise further if you want to share more about the other complexities.

However! If you must, the formula you want on the Available column is

[Table 1].Find(currentValue.ItemNumber = thisRow.ItemNumber).QtyOnHand > 0
1 Like
7

I was able to get it to work using

If(thisRow.[Item Number].Contains([Inventory Table].[Item Number]),“Active” ,’’ )

I think I was overthinking it yesterday and got frustrated. Thanks for all of the help.

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.