Skip to main content

I cannot seem to get the relations working on this table, and im baffled since it seems like it should be as simple as simple gets.

Case: I am making a nested GI, all the sub-GI’s work fine. they consist of a date column and a column showing the needed value for the item in question.

The issue is each day will only have one or two columns of data, so as a foundation/starting point for the nested GI im using the SM.DateInfo table, then making relations to the sub-GI’s using a left join.

 

This connection simply will not work. I tried using just the DateInfo and SOOrder table and doing a similar left join, hoping a stock table would work, and nothing. I even tried to convert the date on other tables to an integer and join with the DateInfo.DateInt field to no avail.

As a separate test i made a GI with just the DateInfo table, have the results grid show just the date field, and tried to put conditions on it to pare down the results. Between Today()-400 and Today(). i get no results. Again, no filters or anything i compare against the date field works with the exception of parameters that are populated From Schema. I suspect the displayed date isnt the actual data in the field which is why no connections or filter values are working.

Am I missing something about this table? should I be using a different table as a starting point? I cannot get ANYTHING to work in relation to it.

​@ChrisB While I am still not sure what you mean the connection does not work, the Date field on DateInfo is being stored as Date and time format on DB though the time for all records is just 00:00:00.000 which is not the same for other tables like SOOrder and they do have actual time set for the date fields. 

Therefore you can use something like this when link the two tables:

 

=Left(LSOOrder.CreatedDateTime],12) equals =Left(=DateInfo.Date],12) it works for me.

 

By “connection not working” i mean its not sucessfully joining. so no data at all is being pulled from my nested-GI’s. The time field entry you mentioned sounds 100% like the culprit thats causing me grief.

Your method using the left function worked great using the POOrder test. The last hurdle is how to link the nested-GI’s. The date column on the Nested GI is based on the INTran.ReleasedDateTime. But the GI itself is named “testpowderblackmonthly”.

when using a nested-GI how is that conveyed as its not in the Fields list and doesnt seem to follow the nomenclature of o<table/inquiry>.<columntitle>]. 

I tried a few variations:

left(otestpowderblackmonthly.INTran_releasedDateTime],12)
left(]testpowderblackmonthly.releasedDateTime],12)

I even tried not using the left function on this side of the join.

Okay, worked this one out. 

The Nested GI’s had spaces in the names, so even though it was displaying as “testpowderblackmonthly” that wasn’t what the GI’s actual ID was.

Once i renamed them to remove the space, i could use the “left( testpowderblackmonthly.INTran_releasedDateTime],12)” to make the link. So this is now resolved.

Thanks for putting me on the right track HKabiri, and showing me a bit more of how the behind-the-scenes data works. 👍

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved